Calendar Date Grouping by row counts

1

I'm trying to group a series of weekly date ranges for financial reporting and I need to group those weeks into a repeating dataset where every 4 records are grouped into the same category 13 times and then the category repeats.

So here's the dataset I'm trying to create:

FirstDayOfWeek LastDayOfWeek ActGrp
2019-01-07 2019-01-13 1
2019-01-14 2019-01-20 1
2019-01-21 2019-01-27 1
2019-01-28 2019-02-03 1
2019-02-04 2019-02-10 2
Same pattern of groups of 4 counting incrementally for the year excluded for space
2019-12-02 2019-12-08 12
2019-12-09 2019-12-15 13
2019-12-16 2019-12-22 13
2019-12-23 2019-12-29 13
2019-12-30 2020-01-05 13
2020-01-06 2020-01-12 1 **After the 4 rows in the 13th group, restart numbering at 1 and continue until my calendar table runs out of dates

Here's my query from a standard calendar table to get my FirstDay and LastDay, I'm just having trouble thinking of how to create the ActGrp numbering in SQL.

;
WITH Monday AS (
SELECT CalendarDate FROM Calendar where DayOfWeekName = 'Monday' AND CalendarDate > '2018-12-31'
), Sunday AS (
SELECT CalendarDate FROM Calendar where DayOfWeekName = 'Sunday'
)

SELECT M.CalendarDate FirstDayOfWeek, S.CalendarDate LastDayOfWeek
FROM Monday M INNER JOIN Sunday S ON DATEDIFF(dd,S.CalendarDate,M.CalendarDate) = -6
ORDER BY M.CalendarDate

(Current plan is to dump into Excel and manually create my inserts, but I was hoping for a more elegant solution.)

2 
;WITH 
Mondays AS (
    SELECT CalendarDate FROM Calendar 
    WHERE DayOfWeekName = 'Monday' AND CalendarDate > '2018-12-31'
)
SELECT FirstDayOfWeek, LastDayOfWeek, (CASE WHEN row_num % 52 = 0 THEN 52 
           ELSE row_num % 52 END + 3) / 4 AS ActGrp
FROM (
    SELECT 
        M.CalendarDate FirstDayOfWeek, 
        DATEADD(DAY, 6, M.CalendarDate) AS LastDayOfWeek,
        ROW_NUMBER() OVER(ORDER BY M.CalendarDate) AS row_num
    FROM Mondays M
) AS query1
ORDER BY FirstDayOfWeek
2 Likes
3

Perfect!

I knew I was going to have to use a % 52 somehow to try and get down to groups of 13 but I was heading down a wormhole with multiple other levels of CTE's to try and do the math, this is exactly what I was trying to do all in one shot.

Thanks again!

4

You're welcome!