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

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;