FREE AI-Enhanced Online SQL Server Compiler

Untitled*

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

-- Our test data, with each day of year 2025.
create table d (d date);
with
	-- First a list of days to test:
    dd as
    (
        select cast('2024-12-25' 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 380);

-- Rule telling which weeks starts the year:
-- Which day does the week begin on? Use datepart(dw) notation: 1 for Sunday, 7 for Satursday.
declare @firstDayOfWeek int = 1;
-- Let's say the fiscal week starts on sundays, and we have a Sunday, December 29th;
-- @firstWeekRule can be set to:
-- - first day in new year: first week is January 5th to 11th (because the first sunday of 2025 is on the 5th)
-- - last day in new year: first week is December 29th to January 4th (because the satursday that closes that week is in 2025)
-- - most days in new year: same as previous case, because 4 days of the week are in 2025 versus 3 days in 2024
declare @firstWeekRule varchar(31) = 'most days in new year';

with
	-- Get distinct legal years from dates, as well as years before and after
    -- (because some days around New Year's Eve will belong a fiscal year different from their legal year)
    years as
    (
      	select distinct year(d) y from d
      	union all select min(year(d)) - 1 from d
      	union all select max(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 *,
      		case 
      			when
      				@firstWeekRule = 'first day in new year'
      				or
      				(
                      	@firstWeekRule = 'most days in new year'
                      	and (7 + @firstDayOfWeek - datepart(dw, legalstart)) % 7 < 3
                    )
      			then dateadd(day, (7 + @firstDayOfWeek - datepart(dw, legalstart)) % 7, legalstart)
      			else dateadd(day, -(7 + datepart(dw, legalstart) - @firstDayOfWeek) % 7, legalstart)
      		end 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,
      		legalstart,
      		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 m, format(dateadd(month, m, legalstart), 'MMMM') month, min(d) fromday, max(d) today, count(distinct w) n_weeks
from w group by y, m, legalstart order by y, m;

y	m	month	fromday	today	n_weeks
2024	12	December	2024-12-25	2024-12-28	1
2025	1	January	2024-12-29	2025-01-25	4
2025	2	February	2025-01-26	2025-02-22	4
2025	3	March	2025-02-23	2025-03-29	5
2025	4	April	2025-03-30	2025-04-26	4
2025	5	May	2025-04-27	2025-05-24	4
2025	6	June	2025-05-25	2025-06-28	5
2025	7	July	2025-06-29	2025-07-26	4
2025	8	August	2025-07-27	2025-08-23	4
2025	9	September	2025-08-24	2025-09-27	5
2025	10	October	2025-09-28	2025-10-25	4
2025	11	November	2025-10-26	2025-11-22	4
2025	12	December	2025-11-23	2025-12-27	5
2026	1	January	2025-12-28	2025-12-31	1

(372 rows affected)

(14 rows affected)