Hi,

 

I am having trouble summing a found query in my SQL select statement.

 

( Select Sum ( AmountPaid )

 

From Consult

 

Where ? Between SearchDate1 and SearchDate2 "

 

; "" ; "" ;

 

ConsultDate )

 

I get a result from the entire datafile.  I just want to sum my query between my two date range.  When I type in Jan 1 thru Jan 31 I get the entire year summed ??

 

Please forgive the syntax error, I can not copy the exact SQL Select Statement till later tonight, if you see an obvious error in the overall SQL please ignore, I'm writing from memory and I'm very new to SQL.

 

Can someone provide a basic SQL that sums only records within a date range for me to reference. 

 

Why would the above SQL sum the entire year when I have a range selected ??

 

I've been trying different functions, tried using my Summary Fields et cetera … I would be grateful :-)

 

Thank you.

 

Tom :-)

So your date is exactly "Jan 1" and "Jan 31" ?

 

When using SQL queries you need to format dates in SQL format YYYY-MM-DD

 

However in the past I've had trouble with that as well but I can't remember right exactly why.

 

My habit now is always have a calculation field for all dates.

 

So if you have start date and end date, I'd also have:

 

start_date_asnumber = Calc: getasnumber(start_date)

end_date_asnumber = Calc: getasnumber(end_date)

 

Once all dates are stored as numbers, using ExecuteSQL statements on the numbers is MUCH easier.

 

Using numbers is also faster, as well as faster when sorting via relationships.

I think you're better off using a syntax that allows you to specify the cutoff dates as parameters:

 

Let(

 

[

 

_sql =

"SELECT SUM( amountPaid ) FROM consult WHERE consultDate >= ? AND consultDate <= ?" 

 

];

 

ExecuteSQL( _sql ; "" ; "" ; Date ( 1 ; 1 ; 2013 ) ; Date ( 1 ; 3 ; 2013 ) )

 

)

 

See result in screenshot:

 

The dates of course can be fed from other fields or variables, I just hard coded them for the demo.

truelifeajf and Wim Decorte

 

To me it looks like you will achieve the same outcome in two different ways,

though Decort's approach leaves a more legible script.

Yes, the most best way of using the ExecuteSQL statements query to be handled using the Variables using the let function. I prefer it most.

 

Instead of using the Execute SQL sum function, its better to get the value list extracted from execute SQL statement and using a custom function to total up the values in the list.

 

As the SQL SUM function would make the the application process slow if we have a large amount of records.