CODEWITHSUNDEEP
sqlsql-server
Héléna
Héléna

Reputation: 1095

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

Upvotes: 0

Views: 158

Answers (1)

David Rushton
David Rushton

Reputation: 5030

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.

Upvotes: 1

Related Questions