Aggregating daily data to monthly data SQL

tikkimasala 0
Viewed 40

I have a list of road incidents that vary based on type (breakdown, crashes, fire, hazard etc.). Currently I have them as daily data but I need to aggregate it into monthly data. A month is defined as every 30 days, so if I have a date range from 03/03/2021 - 27/05/2021, the expected output would be:

Month Date Start Date End Type Incident Count
1 03.03.21 01.04.21 Breakdown 20
2 02.04.21 01.05.21 Breakdown 30
3 02.05.21 31.05.21 Breakdown 55
1 03.03.21 01.04.21 Crash 20
2 02.04.21 01.05.21 Crash 30
3 02.05.21 31.05.21 Crash 55

Note how in the Date End, I have gone beyond the end date range (27/05/2021) and included data from 28/05/2021 - 31/05/2021 in order to complete the month and include 30 days.

Input table

Incident Date Type
03.03.21 Breakdown
04.03.21 Crash
05.03.21 Fire
06.03.21 Breakdown
07.03.21 Breakdown

Etc.

aggregate sql-server sql
1 Answers

A solution consist in building a calendar with a recusive CTE query, and join this calendar to the incident table and perform a GROUP BY.

Here is a complete sample with data generation (at the end):

DECLARE @startDate date = DATEFROMPARTS(2021, 3, 3);
DECLARE @endDate date = DATEFROMPARTS(2021, 12, 30);

WITH dateRange AS
(
    SELECT  1 AS MonthNumber,
            @startDate AS MonthStartDate,
            IIF(DATEADD(DAY, 29, @startDate) > @endDate, @endDate, DATEADD(DAY, 29, @startDate))  AS MonthEndDate
    UNION ALL
    SELECT  1 + dra.MonthNumber,
            DATEADD(DAY, 1, dra.MonthEndDate),
            IIF(DATEADD(DAY, 29, DATEADD(DAY, 1, dra.MonthEndDate)) > @endDate, @endDate, DATEADD(DAY, 29, DATEADD(DAY, 1, dra.MonthEndDate)))  AS MonthEndDate
    FROM dateRange dra
    WHERE dra.MonthEndDate < @endDate AND dra.MonthNumber < 100
)
SELECT  dra.MonthNumber,
        dra.MonthStartDate,
        dra.MonthEndDate,
        inc.IncidentType,
        COUNT(1) AS IncidentCount
FROM #incident inc
        INNER JOIN dateRange dra ON inc.IncidentDate BETWEEN dra.MonthStartDate AND dra.MonthEndDate
GROUP BY dra.MonthNumber, dra.MonthStartDate, dra.MonthEndDate, inc.IncidentType;

Data generation:

CREATE TABLE #incident
(
    IncidentDate    date NOT NULL,
    IncidentType    varchar(50) NOT NULL
);

INSERT INTO #incident
VALUES  (DATEFROMPARTS(2021, 3, 3), 'Breakdown'),
        (DATEFROMPARTS(2021, 3, 4), 'Crash'),
        (DATEFROMPARTS(2021, 3, 5), 'Fire'),
        (DATEFROMPARTS(2021, 3, 6), 'Breakdown'),
        (DATEFROMPARTS(2021, 3, 7), 'Breakdown'),
        (DATEFROMPARTS(2021, 3, 7), 'Breakdown'),
        (DATEFROMPARTS(2021, 3, 8), 'Crash'),
        (DATEFROMPARTS(2021, 3, 9), 'Fire'),
        
        (DATEFROMPARTS(2021, 5, 3), 'Breakdown'),
        (DATEFROMPARTS(2021, 5, 4), 'Crash'),
        (DATEFROMPARTS(2021, 5, 5), 'Fire'),
        (DATEFROMPARTS(2021, 5, 6), 'Breakdown'),
        (DATEFROMPARTS(2021, 5, 7), 'Breakdown'),
        (DATEFROMPARTS(2021, 5, 7), 'Breakdown'),
        (DATEFROMPARTS(2021, 5, 8), 'Crash'),
        (DATEFROMPARTS(2021, 5, 9), 'Fire'),

        (DATEFROMPARTS(2021, 7, 3), 'Breakdown'),
        (DATEFROMPARTS(2021, 7, 4), 'Crash'),
        (DATEFROMPARTS(2021, 7, 5), 'Fire'),
        (DATEFROMPARTS(2021, 7, 6), 'Breakdown'),
        (DATEFROMPARTS(2021, 7, 7), 'Breakdown'),
        (DATEFROMPARTS(2021, 7, 7), 'Breakdown'),
        (DATEFROMPARTS(2021, 7, 8), 'Crash'),
        (DATEFROMPARTS(2021, 7, 9), 'Fire');

Isolated calendar creation:

DECLARE @startDate date = DATEFROMPARTS(2021, 3, 3);
DECLARE @endDate date = DATEFROMPARTS(2021, 12, 30);

WITH dateRange AS
(
    SELECT  1 AS MonthNumber,
            @startDate AS MonthStartDate,
            IIF(DATEADD(DAY, 29, @startDate) > @endDate, @endDate, DATEADD(DAY, 29, @startDate))  AS MonthEndDate
    UNION ALL
    SELECT  1 + dr.MonthNumber,
            DATEADD(DAY, 1, dr.MonthEndDate),
            IIF(DATEADD(DAY, 29, DATEADD(DAY, 1, dr.MonthEndDate)) > @endDate, @endDate, DATEADD(DAY, 29, DATEADD(DAY, 1, dr.MonthEndDate)))  AS MonthEndDate
    FROM dateRange dr
    WHERE dr.MonthEndDate < @endDate AND dr.MonthNumber < 100
)
SELECT  MonthNumber,
        MonthStartDate,
        MonthEndDate
FROM dateRange;
Rom Eh0
Related