user2436996 user2436996 - 1 month ago 6
SQL Question

Group data by latest date per month

I store data on a daily basis in the following table

CREATE TABLE dbo.DemoTable
(
ReportDate DATE NOT NULL,
IdOne INT NOT NULL,
IdTwo INT NOT NULL,
NumberOfThings INT NOT NULL DEFAULT 0

CONSTRAINT PK_DemoTable PRIMARY KEY NONCLUSTERED (ReportDate, IdOne, IdTwo)
)


I'd like to report on this but only pull out data (sum of NumberOfThings) for the latest date we have for each month.

Example data

INSERT INTO DemoTable
(ReportDate, IdOne, IdTwo, NumberOfThings)
VALUES
('2016-11-02',1,2,2), ('2016-11-02',1,3,2), ('2016-11-01',1,2,20), ('2016-11-01',1,3,20),
('2016-10-31',1,2,2), ('2016-10-31',1,3,2), ('2016-10-30',1,2,20), ('2016-10-30',1,3,20), ('2016-10-29',1,2,200), ('2016-10-29',1,3,200),
('2016-09-30',1,2,5), ('2016-09-30',1,3,5), ('2016-09-29',1,2,55), ('2016-09-29',1,3,55)


So for this data I want to see:

2016-11-02 | 4
2016-10-31 | 4
2016-09-30 | 10


Thanks

Answer

You can use RANK() to spot the latest date rows on each month, and them sum them .

SELECT t.ReportDate,SUM(t.NumberOfThings)
FROM (
    SELECT t.*,
           RANK() OVER(PARTITION BY YEAR(t.ReportDate), MONTH(t.ReportDate) ORDER BY t.ReportDate DESC) as rnk
    FROM DemoTable t) s
WHERE s.rnk = 1
GROUP BY s.ReportDate