Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
This should work:
MonthName(Posting_Date) = MonthName(AddMonths(Today(), -1))
This should work:
MonthName(Posting_Date) = MonthName(AddMonths(Today(), -1))
Or
MonthName(Posting_Date) = MonthName(Today(), -1)
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)?
Hi Rusians,
The answer to your final question is affirmative, the MonthName function include the year, so it will target (select) the previous month-year.
HTH
Thank you very much!
It is really helpful information!
Doesn't work without Addmonth. I used syntax from previous comment.
But thank you anyway!
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)
;