Sql Server 创建财务会计445日历（完美终极版）

上一篇在我的随笔中记录了一个由老外用sql写的会计日历（Fiscal Calendar), 但是它有很多不完善的地方，比如不根据当前日期来推导前后，只能自已指定，没有期间数，没有期间的周数，没有单独的字段来存放每个期间开始和结束的日期，再就是年末年头的处理，一月份中是有大小月，一月第一周和上年的第53周是一起的情况，所以第一周有长有短，有的第一周有1天，有的有6天，而现实中有的公司会把大于4天的第一周算作单独的一个周，而小于4天的是第一周则不算单独一个周，那么在确定这一年的第一个期间的结束日期就不会遵守445的原则，需是采取545作为第一季度循环。那么按原作者的方法生成的日历在有些年份期末日期的确定就会与实际产生偏差，我根据老外的那个版本的缺失部做了一些加强，算是完美的解决了上面的一些问题。下面是实际代码

 

SET DATEFIRST 7;
DECLARE @StartDate DATE  = DATEADD(yy,  DATEDIFF(yy,0,getdate())-9,  0);
DECLARE @EndDate DATE    = dateadd(yy, datediff(yy,0,getdate())+3, 0);
DECLARE @MonthEndDay INT = 7; -- Saturday
DECLARE @BigMonth INT = 4;
-- 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))
   ----select t from 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)
   --select * from d ;
    ,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
                            ---detemin the first week of year is big and small
                            ---becuase some week which new year's day is Sunday,so then cycle mothed is 445
                            --- weeknum   1    -    2    -    3    -    4    -    5    -    6    -    7                 weeknum   1    -    2    -    3    -    4    -    5    -    6    -    7     weeknum   1    -    2    -    3    -    4    -    5    -    6    -    7 
                            --              日        一        二        三        四        五        六                      日        一        二        三        四        五        六               日        一        二        三        四        五        六
                            --        1      1                                                                        0            1                                                    0                                    1       2       3
                            --        2                                                                            1                                                                1                
                            --        3                                                                            2                                                                2                
                            --        4                                                                            3                                                                3                        
                            --                                                                                                                                                    4
                            case when datepart(weekday,DATEADD(yy,  DATEDIFF(yy,0,d),0)) between 2 and @BigMonth
                            THEN
                            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 (3,7,12,16,20,25,29,33,38,42,46,52)
                                   AND datepart(weekday,d) = @MonthEndDay
                               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
                       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
                   --Get date in fiscal period

              FROM d
              )
    ,e     AS(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
      --Get date in fiscal period
                        ,sum(FiscalPeriodEndDate) over (partition by year(FullDate) order by FullDate) + 1 - FiscalPeriodEndDate as FiscalPeriodNumber

      --Get date In Period Week Number
      --,sum(WeekNum) over (partition by WeekNum  order by FullDate) as FiscalPeriodWeekNum
         -- 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 
             
             ) 


SELECT             FullDate
                ,FiscalPeriodEndDate
                ,WeekNum
                ,QuarterNum
                ,FiscalPeriodNumber
                -- Caculate the week number in fiscal period.
                ,ROUND(RANK() OVER (PARTITION BY FiscalPeriodNumber,DateYear order by DateYear,WeekNum )/7,0)+1  as FiscalPeriodWeekNum
                ,DateYear
                ,DateMonth
                ,DateDay
                ,DateWeekDayNum
                ,DateWeekDayName
                ,Min(FullDate) over(partition by FiscalPeriodNumber,DateYear order by FiscalPeriodNumber) as startPeriod
                ,Max(FullDate) over(partition by FiscalPeriodNumber,DateYear order by FiscalPeriodNumber) as endPeriod
FROM e
ORDER BY FullDate ;

上面是代码是采用的分层设计，迭代实现的步骤来完全。

首先

第一层是实现了一个1到10的序列

　　　　第二层实现了从开始年份一月一日到结果年份12月31号的一个日期列表

　　　　　　　第三层是根据日期列表计算出期间结束日期，和其它属性（WeekNum和QuarterNum）

　　　　　　　　　　第四层再根据上一层的期间结整日期计算出期间号

　　　　　　　　　　　　　　第五层再根据期间号计算出期间内的week编号，和每一天的所在期间的开始和结束日期（这里是有点冗余，但方便以后查询）。

这个代码还是有一些不完美的地方，比如相对第0周，期间周编号会把第0周和第一周的天数合并到一周（当然这也合乎一些公司的设定），更合理的是应当把大周单独编为一周，小周则合并，先看看吧，以后再有机会改进，现在的代码已经满足大部份人的要求了。