如何用T-SQL从日历年和动态会计起始月生成含月周数的会计日历？

需求概述 #

根据指定的日历年和动态会计年度起始月份，生成包含会计月起止日期、会计月内周数、每周起止日期等信息的会计日历，支持自定义每周起始日（周一/周日等）。

实现思路 #

1. 确定目标会计年度的完整时间范围：根据输入的日历年和财年起始月，计算财年的开始与结束日期
2. 生成财年内的所有日期序列：用递归CTE生成连续日期
3. 计算每个日期对应的会计维度：会计年、会计月、会计周
4. 聚合统计每个会计月的周数，并关联每周的起止日期
5. 支持自定义每周起始日（默认周一，可通过参数调整）

完整T-SQL代码 #

DECLARE 
    @CalendarYear INT = 2024,          -- 指定日历年
    @FiscalStartMonth INT = 4,         -- 会计年度起始月份（1-12）
    @WeekStartDay INT = 1;             -- 每周起始日：1=周一，7=周日

-- 计算会计年度的起止日期
DECLARE 
    @FiscalYearStart DATE = DATEFROMPARTS(@CalendarYear, @FiscalStartMonth, 1),
    @FiscalYearEnd DATE;

SET @FiscalYearEnd = DATEADD(DAY, -1, DATEADD(MONTH, 12, @FiscalYearStart));

-- 递归CTE生成会计年度内的所有日期
WITH DateRange AS (
    SELECT @FiscalYearStart AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM DateRange
    WHERE [Date] < @FiscalYearEnd
),
-- 计算每个日期的会计维度和周信息
DateDimensions AS (
    SELECT
        [Date],
        -- 计算会计年：如果当前月份 >= 财年起始月，会计年=日历年；否则=日历年-1
        CASE 
            WHEN MONTH([Date]) >= @FiscalStartMonth THEN YEAR([Date])
            ELSE YEAR([Date]) - 1
        END AS FiscalYear,
        -- 计算会计月：从财年起始月开始计数，比如起始月4月，4月=1，5月=2...3月=12
        ((MONTH([Date]) - @FiscalStartMonth + 12) % 12) + 1 AS FiscalMonth,
        -- 计算周数：基于指定的每周起始日
        DATEPART(WEEK, DATEADD(DAY, (@WeekStartDay - DATEPART(WEEKDAY, [Date])) % 7, [Date])) AS FiscalWeek,
        -- 计算本周起始日
        DATEADD(DAY, (@WeekStartDay - DATEPART(WEEKDAY, [Date])) % 7, [Date]) AS WeekStartDate,
        -- 计算本周结束日
        DATEADD(DAY, (@WeekStartDay - DATEPART(WEEKDAY, [Date])) % 7 + 6, [Date]) AS WeekEndDate
    FROM DateRange
),
-- 去重每周信息
UniqueWeeks AS (
    SELECT DISTINCT
        FiscalYear,
        FiscalMonth,
        FiscalWeek,
        WeekStartDate,
        WeekEndDate
    FROM DateDimensions
)
-- 最终输出：会计月统计 + 每周明细
SELECT
    uw.FiscalYear,
    uw.FiscalMonth,
    -- 会计月名称（如"2024财年第1月"）
    CONCAT(uw.FiscalYear, '财年第', uw.FiscalMonth, '月') AS FiscalMonthName,
    -- 会计月起止日期
    MIN(dr.[Date]) AS FiscalMonthStartDate,
    MAX(dr.[Date]) AS FiscalMonthEndDate,
    -- 会计月内周数
    COUNT(DISTINCT uw.FiscalWeek) OVER (PARTITION BY uw.FiscalYear, uw.FiscalMonth) AS WeekCountInMonth,
    uw.FiscalWeek,
    uw.WeekStartDate,
    uw.WeekEndDate
FROM UniqueWeeks uw
JOIN DateRange dr ON dr.[Date] BETWEEN uw.WeekStartDate AND uw.WeekEndDate
GROUP BY uw.FiscalYear, uw.FiscalMonth, uw.FiscalWeek, uw.WeekStartDate, uw.WeekEndDate
ORDER BY uw.FiscalYear, uw.FiscalMonth, uw.FiscalWeek
OPTION (MAXRECURSION 0);

代码说明 #

1. 参数配置：

   • @CalendarYear：指定要生成的日历年（如2024）
   • @FiscalStartMonth：会计年度的起始月份（1-12，比如4代表财年从4月开始）
   • @WeekStartDay：自定义每周起始日，1=周一，7=周日，可根据需求调整

2. 会计年度范围计算：

   • 财年开始日期为指定日历年的起始月份第一天
   • 财年结束日期为开始日期加12个月再减1天，确保覆盖完整12个会计月

3. 日期维度计算：

   • FiscalYear：判断当前日期所属的会计年度（若日期在起始月之前，会计年为日历年-1）
   • FiscalMonth：从财年起始月开始计数，将自然月转换为会计月序号（如4月=1，3月=12）
   • FiscalWeek：基于指定的每周起始日计算周序号，避免自然周跨会计月的问题

4. 结果输出：

   • 包含会计年、会计月、会计月名称、会计月起止日期、月内周数
   • 同时列出每个会计月内的所有周的起止日期，便于后续业务使用

测试示例 #

如果需要生成2024年、财年从4月开始、每周从周一开始的会计日历，直接执行上述代码即可。输出结果会包含2024财年（2024.4.1-2025.3.31）每个会计月的周数统计及每周明细。

内容的提问来源于stack exchange，提问作者vbjay