AnsweredAssumed Answered

Using ExecuteSQL to SUM records

Question asked by MichaelGallagher on Jul 23, 2013
Latest reply on Jul 23, 2013 by LaRetta

Hello

 

I'm having an issue with ExecuteSQL

 

I'm trying to SUM the Hours for a Job AND WorkDate, in one statement

 

The Table contains thousands of records, but only two records match the desired Job AND WorkDate

 

The following OR statement succeeds and produces a valid numerical result:

ExecuteSQL ( "SELECT SUM (Hours_Project) FROM Tm_ProjectHours WHERE Job = ? OR WorkDate =?" ; "" ; ""; 206950; "6/14/2013")

[RESULT = 194.12] where Job is 206950 and WorkDate is "6/14/2013"

 

However, the following very similar AND statement fails and produces a NULL result:

ExecuteSQL ( "SELECT SUM (Hours_Project) FROM Tm_ProjectHours WHERE Job = ? AND WorkDate =?" ; "" ; ""; 206950; "6/14/2013")

[RESULT NULL] where Job is 206950 and WorkDate is "6/14/2013"

 

I have tried simplifying the statement by deleting either the Job or the WorkDate following the WHERE, and either simplification produces a valid SUM, although not the SUM of only the two records of interest. For this reason, I don't believe I have an issue with date or quote formatting.

 

Any ideas on how I can get the SUM of the two records I want, without first isolating a found set?

 

Thanks!

Outcomes