FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

create table d (d date);
with
	-- First a list of days to test:
    dd as
    (
        select cast('2025-01-01' as date) d
        union all
        select dateadd(day, 1, d) from dd where d < '2025-12-31'
    )
insert into d select * from dd option (maxrecursion 367);

with
	-- Get distinct legal years from dates, add the previous year in case we have a 1st of January in our dates, to fiscally attached to the previous year.
    years as (select distinct year(d) y from d union all select min(year(d)) - 1 from d),
    -- Legal years.
    ly as (select y, dateadd(year, y - 1900, 0) legalstart from years),
    -- Fiscal years starts.
    fys as
    (
      	select *, dateadd(day, 7 - datepart(dw, legalstart), legalstart) fiscalstart
      	from ly
    ),
    -- Fiscal year ends one day before next year's fiscal start, or by default on December 31st (1 day before January 1st).
    fy as
    (
      	select *, dateadd(day, -1, coalesce(lead(fiscalstart) over (order by y), dateadd(year, 1, legalstart))) fiscalend
        from fys
    ),
    -- Now count weeks as number of days divided by 7.
    w as
    (
      	select
      		d,
      		y,
      		datediff(day, fiscalstart, d) / 7 w,
      		-- Month is roughly week number divided by 4.
			-- However we have to transpose our 13 4-week months to a 12 month year, thus / 13 * 12.
      		-- We do this division the other way round, to benefit from euclidian divides that return integers, thus putting us in "full" months instead of inbetween.
      		-- But if we stop here, we get trimesters of 5-4-4 weeks, as the * 12 / 13 will make the 5th week below the threshold of 4 (not big enough to make it to February).
      		-- Thus we introduce an artificial shift of 2/3 of a month (( + 2/3) * 12) / 13), so that week 5 and 9 get postponed to a month later.
      		((datediff(day, fiscalstart, d) / 7) * 12 + 8) / 13 / 4 m
        from d join fy on d.d between fy.fiscalstart and fy.fiscalend
    )
select y, m + 1, min(d) fromday, max(d) today, count(distinct w) n_weeks
from w group by y, m order by y, m;

y		fromday	today	n_weeks
2024	12	2025-01-01	2025-01-03	1
2025	1	2025-01-04	2025-01-31	4
2025	2	2025-02-01	2025-02-28	4
2025	3	2025-03-01	2025-04-04	5
2025	4	2025-04-05	2025-05-02	4
2025	5	2025-05-03	2025-05-30	4
2025	6	2025-05-31	2025-07-04	5
2025	7	2025-07-05	2025-08-01	4
2025	8	2025-08-02	2025-08-29	4
2025	9	2025-08-30	2025-10-03	5
2025	10	2025-10-04	2025-10-31	4
2025	11	2025-11-01	2025-11-28	4
2025	12	2025-11-29	2025-12-31	5

(365 rows affected)

(13 rows affected)