Date constraints in LOAD

[sculptorlv]

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!

[jonathandienst · Accepted solution]

This should work:

MonthName(Posting_Date) = MonthName(AddMonths(Today(), -1))

[#3]

Or

MonthName(Posting_Date) = MonthName(Today(), -1)

[sculptorlv]

That is the difference between MonthName and Month ?

Are you sure, I will get data only from previous month, but not from date with same months (from several different years)?

[ArnadoSandoval]

Hi Rusians,

• The function Month returns the name of the month.
• While the function MonthName returns MMM YYYY ( click here )

The answer to your final question is affirmative, the MonthName function include the year, so it will target (select) the previous month-year.

HTH

[sculptorlv]

Thank you very much!

It is really helpful information!

[sculptorlv]

Doesn't work without Addmonth. I used syntax from previous comment.

But thank you anyway!

[#8]

Not important as you have a solution, but the offset in the monthname function does work - i.e. it does work without the additional addmonths call - I think it might be due to inconsistent date formats in your data model or load script.  The extra call shouldn't matter unless you're dealing with very large datasets where every last bit of performance counts!

Try this in a new document (I ran the below in Jul-17):

SET DateFormat='DD-MMM-YYYY';

PostingDate:

LOAD

  *

INLINE [

PostingDate

01-JUN-2017

01-JUL-2017

01-AUG-2017

01-SEP-2017

];

PostingDateFiltered:

LOAD

  PostingDate as PostingDateFiltered

Resident

  PostingDate

WHERE

  MonthName(PostingDate) = MonthName(Today(),-1)

;