I need to group the sales date starting from a certain date. The date is determined by the date when the sales data is exported. In the example below is the data is exported on 2016/11/10.
Then the sales date should be grouped into the yellow column to calculate the sales during each period.
I tried the dateadd function, but since the each month might have different different days, so it doesn't work.
Any suggestion would be greatly appreciated. Thanks.
The key to this challenge is finding something you can group on. As this doesn't exist in the data you will need to create it. My basic idea is to offset the date. Days that fall after the current day-of-month are pushed into the following month. This gives us a reporting month, which we can group on.
My query uses the following sample data:
-- Generates a sample table. -- Contains one record per day, between Dec 1st 2015 and Feb 29th 2016. DECLARE @BaseDate DATE = '2015-12-01'; DECLARE @Sample TABLE ( DateKey DATE ) ; WHILE @BaseDate < '2016-02-29' BEGIN INSERT INTO @Sample ( DateKey ) VALUES (@BaseDate) ; SET @BaseDate = DATEADD(DAY, 1, @BaseDate); END
And here it is:
-- Offsets the date into reporting month groups. SELECT DateKey, CASE WHEN DAY(DateKey) > DAY(GETDATE())THEN CASE MONTH(DateKey) WHEN 12 THEN DATEFROMPARTS(YEAR(DateKey) + 1, 1, 1) ELSE DATEFROMPARTS(YEAR(DateKey), MONTH(DateKey) + 1, 1) END ELSE DATEFROMPARTS(YEAR(DateKey), MONTH(DateKey), 1) END AS ReportingMonth FROM @Sample WHERE DateKey BETWEEN '2015-12-01' AND '2016-04-30' ORDER BY DateKey DESC ;
If you run this on the 24th of a month the results look like:
DateKey ReportingMonth 2016-02-28 2016-03-01 2016-02-28 2016-03-01 2016-02-27 2016-03-01 2016-02-26 2016-03-01 2016-02-25 2016-03-01 2016-02-24 2016-02-01 -- Reporting period changes here. 2016-02-23 2016-02-01 2016-02-22 2016-02-01 ... 2015-12-03 2015-12-01 2015-12-02 2015-12-01 2015-12-01 2015-12-01
To be honest I'm not happy with this query. I'm sure it could be improved and simplified. But hopefully, it provides you with a good starting point.