7 Replies Latest reply: Jul 19, 2017 3:17 AM by Graeme Smith RSS

    Date constraints in LOAD

    Ruslans KLimovs

      Hello!

      Once again I got information lack within syntax.

       

       

       

      I have an SQL statement, which get sales dated for last 3 month (from current date):

      DATASOURCE_SALES:

      SQL SELECT

           Posting_Date,

           Sale_Money,

           Client_Number

      FROM

       

                BASE

       

      WHERE

      Posting_Date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0)

      AND

      Posting_Date <=DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

      ;

       

       

      Now, I want to do additional data collection using LOAD function:

      SALES_LAST_MONTH:

      LOAD

           Client_Number,

           SUM(Sale_Money) AS Sale_Money

      RESIDENT DATASOURCE_SALES

      WHERE

           MONTH(Posting_Date) = MONTH(Today())-1

      GROUP BY

           Client_Number;

       

      The question is, how to make correct LOAD function in order not get data from December 2018, if Today = January 2018. How to get exactly data from previous month (taking into account year change)?

       

      I hope my question is clear enough .. thank you in advance for help!