SQL Server中怎么实现445会计期间。

 我的在使用sql的函数计算周数和月份等期间数据都是基于自然月份来的。

然而，有些场合，比如会计年度日历表期间的定义是基于4 - 4 - 5 原则设立。

很显然这种日历与自然月的划分大为不同，那么怎么在Sql中实现这个日历。我在国外的论坛中代到一段代码可以实现。

DECLARE @StartDate DATE  = '20230101';
DECLARE @EndDate DATE    = '20301231';
DECLARE @MonthEndDay INT = 7; -- Saturday

-- Tally table creates all the date values which can then be used as your date dimension.
WITH t(t) AS(SELECT t FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(t))
    ,d(d) AS(SELECT top(SELECT datediff(d,@StartDate,@EndDate)+1) dateadd(d,ROW_NUMBER()OVER(ORDER BY (SELECT NULL))-1,@StartDate)FROM t t1,t t2,t t3,t t4,t t5,t t6)
    ,c    AS(SELECT d AS FullDate
                   ,CASE WHEN MONTH(d) = 12  -- This logic is to handle the final day of the year.
                       THEN CASE WHEN DAY(d) = 31 THEN 1 ELSE 0 END
                       ELSE CASE WHEN SUM(CASE WHEN datepart(weekday,dateadd(d,-1,d)) = @MonthEndDay THEN 1 ELSE 0 END) OVER (partition BY YEAR(d) ORDER BY d) IN(4,8,13,17,21,26,30,34,39,43,47,52)
                                   AND datepart(weekday,d) = @MonthEndDay
                               THEN 1
                               ELSE 0
                               END
                       END AS FiscalPeriodEndDate
                   ,SUM(CASE WHEN datepart(weekday,dateadd(d,-1,d)) = @MonthEndDay THEN 1 ELSE 0 END) OVER (partition BY YEAR(d) ORDER BY d) AS WeekNum
                   ,((SUM(CASE WHEN datepart(weekday,dateadd(d,-1,d)) = @MonthEndDay THEN 1 ELSE 0 END) OVER (partition BY YEAR(d) ORDER BY d)-1) / 13)+1 AS QuarterNum
             FROM d
            )
SELECT FullDate
      ,FiscalPeriodEndDate
      ,WeekNum

         -- Where there is a 53rd week it will show as the 5th Quarter per the calculation above, so change it to 4th.
      ,CASE WHEN QuarterNum > 4 THEN 4 ELSE QuarterNum END AS QuarterNum

         -- Examples of different date functions you can use to make querying and reporting easier and when indexed properly, a lot faster.
      ,YEAR(FullDate) AS DateYear
      ,MONTH(FullDate) AS DateMonth
      ,DAY(FullDate) AS DateDay
      ,datepart(weekday,FullDate) AS DateWeekDayNum
      ,datename(weekday,FullDate) AS DateWeekDayName
FROM c
ORDER BY FullDate;

这段代码还是有点可改进的地点，1，没有根据当前日期推前后10年的功能。一旦日期超表，又得改代码。2，没有计算出日期所在的会计期间PD数。