3 Replies Latest reply on Jul 23, 2013 8:23 PM by LaRetta

    Using ExecuteSQL to SUM records

    MichaelGallagher

      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!

        • 1. Re: Using ExecuteSQL to SUM records
          user10625

          Hi,

           

          You need to wrap your date in GetAsDate() ,  because ExecuteSQL doesn't understand friendly USA date formats. I'm not quite sure what tests you tried in your 2nd-to-last paragraph,  but I think that only one half of your OR query was working.

           

          Try  this:

           

           

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

           

          I think it should work.

           

           

          Christopher Bailey

          Baytaflow

          Boston, MA

          1 of 1 people found this helpful
          • 2. Re: Using ExecuteSQL to SUM records
            MichaelGallagher

            I appreciate the suggestion.  I tried the GetAsDate("6/14/2013") with and without the quotes, and both continue to evaluate as NULL.

             

            But it really helped to take another look, as I discovered the correct year should have been 2012 not 2013.

             

            Three hours I won't get back.  Thanks again.

             

            -Mike

            • 3. Re: Using ExecuteSQL to SUM records

              Hi Mike,

               

              This might come in handy for you ... it is a custom function from Kevin Frank.  It NORMALIZES your dates.  I hope it's helpful.

               

              /* Purpose:  converts a FileMaker date to a SQL-friendly date

                  Author:  Kevin Frank w/ help from Koen Van Hulle

               

               

                  Note 1:  this CF handles US-style (MM/DD/YYYY) and European-style (DD/MM/YYYY) date formats,

                  as well as the date returned by a SQL SELECT statement ('YYYY-MM-DD')

                  Note 2:  this CF works with all versions of FileMaker >= 8.0

               

               

                  Usage:  DateToSQL ( pDate )

               

               

                  Example 1:  DateToSQL ( Get ( CurrentDate ) )  = 

                       DATE '2010-05-25'   ...assuming today's date is 25 May 2010

                  Example 2:  DateToSQL ( $sqlDate )  = 

                       DATE '2010-12-31'   ...assuming $sqlDate = '2010-12-31' or 2010-12-31 (i.e., with or without the single quotes)

              */

               

               

               

               

              Let ( a = Substitute ( pDate ; "'" ; "" ) ;      //   strip off single quotes, if any, in case this is a SQL-style date

               

               

              If ( Middle ( a ; 5 ; 1 ) = "-" and Middle ( a ; 8 ; 1 ) = "-" ;

               

               

                 "DATE '" & a & "'" ;

               

               

              Let ( [ 

                 m = Right ( "00" & Month ( pDate ) ; 2 ) ;

                 d = Right ( "00" & Day ( pDate ) ; 2 ) ;

                 y = Year ( pDate )

              ] ;

               

                 "DATE '" & y & "-" & m & "-" & d & "'"

               

               

              )   //   end let

              )   //   end if

              )   //   end let