3 Replies Latest reply on Aug 7, 2015 10:49 AM by gremlin9297

    ExecuteSQL calculation from entered date to start date

    gremlin9297

      Title

      ExecuteSQL calculation from entered date to start date

      Post

      Hi there,

      I have a ExecuteSQL,

      ("SELECT sum(vs.sick) FROM Vacation_Sick vs WHERE year(vs.\"Date\") = ? AND vs.\"Ee ID\" = ? " ;"" ; "" ; Year(Current_Date) ; Ee ID )

      The calculation works.. for the most part. The problem is that when a date is entered that does not match up with a date already listed the field remains empty. I understand why it does but I was wondering if there was a way for the calculation to work even if there are no dates. Such as 

      1/2/15 - 1

      2/1/15 - 1

      5/6/15 - 1

      I get the total of 3 based off the current date.

      I have a report summary that needs to do the same calculation but instead of working off the current date it works off a report date thats entered.

      ("SELECT sum(vs.sick) FROM Vacation_Sick vs 
      WHERE Year (vs.\"Date\") =  ? 
      AND Month (vs.\"Date\") =  ?  
      AND Day (vs.\"Date\") =  ? 
      AND vs.\"Ee ID\" = ? " 
      ;"" ; "" ; Year(gReport_Date) ; Month(gReport_Date) ; Day(gReport_Date) ; Ee ID )

      .... I get an empty field if I enter a date past 5/6/15. Any help would be appreciated.

      Thank you,

      Eric

        • 1. Re: ExecuteSQL calculation from entered date to start date
          philmodjunk

          The Queries that you use in your examples don't seem to match your thread's title. I don't see a start date and an end date.

          In your last query, it would seem that you could use a WHERE clause of just:

          WHERE vs.\"Date\" = ?

          and use gReport_Date as your parameter.

          But gReport_Date would seem to be a single date so I am at a bit of a loss as to what you want for start date and end date. Perhaps this?

          ("SELECT sum(vs.sick) FROM Vacation_Sick vs 
          WHERE vs.\"Date\" >= ? And vs.\"Date\" <= ?
          ;"" ; "" ; gStartDate ; gEndDate )

          • 2. Re: ExecuteSQL calculation from entered date to start date
            gremlin9297

            Thanks for the help. I was able to use the information your provided to get the correct query, after a few trial and errors. I was actually able to get rid of a start date and just use the end date/report date. Thanks for the help, always good to get a new perspective and way of thinking. The final Query looks like this:

            ExecuteSQL 

            ("SELECT sum(vs.sick) FROM Vacation_Sick vs 
            WHERE (vs.\"Date\") <= ? 
             AND year (vs.\"Date\") = ? 

            AND vs.\"Ee ID\" = ? " 

            ;"" ; "" ; 

            (gReport_Date) ; Year (gReport_Date) ; Ee ID )

            *It needs to only look at the current year being reported. 

             

            Thanks Again!

            • 3. Re: ExecuteSQL calculation from entered date to start date
              gremlin9297

              Thanks for the help. I was able to use the information your provided to get the correct query, after a few trial and errors. I was actually able to get rid of a start date and just use the end date/report date. Thanks for the help, always good to get a new perspective and way of thinking. The final Query looks like this:

              ExecuteSQL 

              ("SELECT sum(vs.sick) FROM Vacation_Sick vs 
              WHERE (vs.\"Date\") <= ? 
               AND year (vs.\"Date\") = ? 

              AND vs.\"Ee ID\" = ? " 

              ;"" ; "" ; 

              (gReport_Date) ; Year (gReport_Date) ; Ee ID )

              *It needs to only look at the current year being reported. 

               

              Thanks Again!