Jump to content

SQL Date Range


This topic is 939 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I'm attempting to perform an SQL query to find out how much has been paid in rent for a given time frame for all rent payments in a given property. I have created extra fields to convert dates to numerical values.

DateNo is a date > numerical field in the Payment table (Payment::Date).

DateStart1 is a date > numerical field in the Property table (Properties::DateStart), as is DateEnd1 (DateEnd).

With a date range entered in DateStart and DateEnd (numerically DateStart1 and DateEnd1, respectively) the following statement gives me a blank result. If I remove the date parameters I get a result, obviously for all payments ever entered. My error seems to be when including dates (or the numerical value of the dates). It's not impossible that some rent payment records may have a blank date, which could be causing this issue (?)

Evaluated from the context of Properties:

ExecuteSQL (
" SELECT Sum (Remittance)
FROM
Payment
WHERE
Cat = 'Rent' AND
Type = 'Payment' AND
DateNo  >=  ? AND
DateNo  <=  ? AND
PropID = ? "
; DateStart1 ; DateEnd1 ; ProppID )

Any ideas?

Link to comment
Share on other sites

49 minutes ago, madman411 said:

I have created extra fields to convert dates to numerical values.

There is no need whatsoever to do that. Filemaker will do all the necessary work for you. Try something like:

ExecuteSQL (
"SELECT Sum (Remittance)
FROM Payment
WHERE Cat = 'Rent' 
AND 
Type = 'Payment' 
AND
Datefield BETWEEN ? AND ? 
AND
PropID = ?" ; "" ; "" ;
DateStart ; DateEnd ; ProppID 
)

In this example, DatefieldDateStart and DateEnd are fields of type Date (or calculation fields with a Date result type).

Untested, because I don't have your file.

--
Do note that the code you posted is missing the fieldSeparator and rowSeparator arguments.

 

Edited by comment
  • Like 1
Link to comment
Share on other sites

22 hours ago, comment said:

There is no need whatsoever to do that. Filemaker will do all the necessary work for you. Try something like:

Thank you comment. As always, worked a treat.

As with most things I try in FMP I attempt to work it out on my own before asking for help. Various reading made me believe SQL works faster with numerical fields rather than "date" fields. Needless to say, now I know at least in this case date fields work just fine.

Link to comment
Share on other sites

8 minutes ago, madman411 said:

Various reading made me believe SQL works faster with numerical fields rather than "date" fields.

I've never heard of this. In any case, the process should  be:

  1. Make it work with the original date fields;
  2. Add the numeric fields and see if it's any faster;
  3. If there's any noticeable improvement, consider if it's worth the extra complexity added to your schema.

 

Link to comment
Share on other sites

@comment - is it possible to have SQL reference a field containing a value list?

Using a checkbox set to allow me to choose which properties are factored into the calculation - ProppID would be changed to the field containing the keys. A new field in the Properties table, say 'gPropertySelect' would contain the checkboxes, show values from field 2. I tested this, but as soon as more than one value is "selected" the calculation breaks. I know it may not be the best solution, but curious if it's possible.

ExecuteSQL (
"SELECT Sum (Remittance)
FROM Payment
WHERE Cat = 'Rent' 
AND 
Type = 'Payment' 
AND
Datefield BETWEEN ? AND ? 
AND
PropID = ?" ; "" ; "" ;
DateStart ; DateEnd ; ProppID 
)

 

Link to comment
Share on other sites

I don't know of a way to supply a multi-key value as an argument. However, you can calculate the sqlQuery itself, so that if the global field has say the values 5, 12 and 26 checked, the calculated query will have:

WHERE 
...
AND
PropID IN (5, 12, 26) ; ...

The content of the IN operator can be calculated easily by substituting the carriage return inside the checkbox field by a comma.

Note that the above example assumes PropID is a Number field; otherwise the values must be single-quoted.

 

Edited by comment
Link to comment
Share on other sites

Is that using the ? clause to determine the IN operator?

AND
ProppID IN (?) ; ... "
... ; gValueListField

or

AND
ProppID IN ( gValueListField ) ; ...

I think I'm missing something simple here.

Link to comment
Share on other sites

On 8/30/2021 at 10:57 PM, madman411 said:

I think I'm missing something simple here.

I am afraid it's not that simple. 

The ? character is a placeholder to be replaced by an argument. What I said is that you cannot supply an argument with more than one value (at least I don't know how). So you need to insert the multiple values into the query yourself. For example, something like:

Let ( [
sqlQuery = "
SELECT Sum (Remittance)
FROM Payment
WHERE Cat = 'Rent' 
AND 
Type = 'Payment' 
AND
Datefield BETWEEN ? AND ? 
AND
PropID IN («propIDs»)" ;

propIDs = Substitute ( gValueListField ; ¶ ; "," )

] ;
ExecuteSQL ( 
Substitute ( sqlQuery ; "«propIDs»" ; propIDs ) ; "" ; "" ; 
DateStart ; DateEnd )
)

 

 

Edited by comment
  • Like 1
Link to comment
Share on other sites

This topic is 939 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.