SQL Date Range

[madman411]

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?

[comment]

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, Datefield, DateStart 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 August 26, 2021 by comment

[madman411]

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.

[comment]

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.

 

[madman411]

@comment you don't offer a training course anywhere by chance?

[madman411]

@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 
)

 

[comment]

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 August 30, 2021 by comment

[madman411]

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.

[comment]

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 September 1, 2021 by comment

[madman411]

Good to know I suppose. Another SQL trick up my sleeve.

Also, I didn't know « » existed as operators(?) inside FMP.

[comment]

12 minutes ago, madman411 said:

I didn't know « » existed as operators(?)

They are not operators. They are characters I use to mark placeholder text. This way I can be sure I don't accidentally substitute something I did not intend.