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.)