如何创建SQL自定义4-4-5财务期间日期表

Question

我正在尝试创建一个SQL脚本来生成自定义的财务日期表，用于2010-2030年之间的报告。该日历是松散的基础上4-4-5日历，除了有具体的规则为期间。

这些规则如下：

• 财政年度总是从1月1日开始。
• 财政年度总是在12月31日结束。
• 每个财政期间(月底)的最后一天总是在星期六。
• 财政季度与海关时期保持一致。

见下文的例子；

2016金融日历(Leap年)

​

​

2017金融日历

​

​

我非常感谢您对创建此表的任何指导，该表具有通常的日期维度以及期间和季度的自定义财政值。

Answer 1

我忽略了您的Sales Flash和Final Results标志，因为您的问题中似乎包含了某种程度的自定义逻辑。如果您还想包括美国假日，您还需要维护一个单独的表中的这些日期，您可以join的因素，在任何日期计算在下表中完成。

撇开这一点不谈，你最大的问题似乎是计算月末的天数，因为这些数据会移动很多。这可以使用窗口函数和提取在理货表中生成的部分Date值来完成。我已经包含了许多不同的date函数，如果您使用这个引用表，您可以使用它作为示例，而不是动态创建引用表。但是，如果要创建静态表，最好只创建一次，然后手动整理特殊日期列表，以确保它们是正确的：

declare @StartDate date  = '20100101';
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;

产出-请注意您的财政期间的FiscalPeriodEndDate标志：

+------------+---------------------+---------+------------+----------+-----------+---------+----------------+-----------------+
|  FullDate  | FiscalPeriodEndDate | WeekNum | QuarterNum | DateYear | DateMonth | DateDay | DateWeekDayNum | DateWeekDayName |
+------------+---------------------+---------+------------+----------+-----------+---------+----------------+-----------------+
| ...etc     |                     |         |            |          |           |         |                |                 |
| 2016-01-28 |                   0 |       4 |          1 |     2016 |         1 |      28 |              5 | Thursday        |
| 2016-01-29 |                   0 |       4 |          1 |     2016 |         1 |      29 |              6 | Friday          |
| 2016-01-30 |                   1 |       4 |          1 |     2016 |         1 |      30 |              7 | Saturday        |
| 2016-01-31 |                   0 |       5 |          1 |     2016 |         1 |      31 |              1 | Sunday          |
| 2016-02-01 |                   0 |       5 |          1 |     2016 |         2 |       1 |              2 | Monday          |
| ...etc     |                     |         |            |          |           |         |                |                 |
| 2016-02-25 |                   0 |       8 |          1 |     2016 |         2 |      25 |              5 | Thursday        |
| 2016-02-26 |                   0 |       8 |          1 |     2016 |         2 |      26 |              6 | Friday          |
| 2016-02-27 |                   1 |       8 |          1 |     2016 |         2 |      27 |              7 | Saturday        |
| 2016-02-28 |                   0 |       9 |          1 |     2016 |         2 |      28 |              1 | Sunday          |
| 2016-02-29 |                   0 |       9 |          1 |     2016 |         2 |      29 |              2 | Monday          |
| ...etc     |                     |         |            |          |           |         |                |                 |
+------------+---------------------+---------+------------+----------+-----------+---------+----------------+-----------------+