Héléna Héléna - 8 months ago 37
SQL Question

SQL:How to group the dates from a certain date of each month

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.

enter image description here

Answer Source

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';
        DateKey DATE

WHILE @BaseDate < '2016-02-29'

    INSERT INTO @Sample

    SET @BaseDate = DATEADD(DAY, 1, @BaseDate);

And here it is:

-- Offsets the date into reporting month groups.
        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)
        ELSE DATEFROMPARTS(YEAR(DateKey), MONTH(DateKey), 1)
    END AS ReportingMonth
    DateKey BETWEEN '2015-12-01' AND '2016-04-30'
    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.