SQL Server How to create SQL custom 4-4-5 Finanical Period date table

bnl4lu3b  于 2023-02-21  发布在  其他
关注(0)|答案(1)|浏览(138)

I'm trying to create a SQL script to generate custom financial date table for reporting between years 2010 - 2030. The calendar is loosely based on a 4-4-5 calendar except there are specific rules for periods.

The rules are as follows;

  • The fiscal year always starts on the 1 of January
  • The fiscal year always ends on the 31 of December
  • The last day of each fiscal period (month end) is always on a Saturday
  • Fiscal quarters are aligned to the custom periods

See examples below;

2016 Financial Calendar (Leap Year)

2017 Financial Calendar

I would really appreciate any guidance on creating this table with the usual date dimensions and the custom fiscal values of period and quarter.

9bfwbjaz

9bfwbjaz1#

I have ignored your Sales Flash and Final Results flags as there appears to be a degree of custom logic around those that you have not included in your question. If you wanted to also include US Holidays, you would also need to maintain a separate table of those dates that you could join onto to factor into any date calculations done in the table below.

That aside, it appears your biggest issue is calculating the Month End days, as these move around a lot. This can be done using Windowed Functions and extracting parts of the Date values generated in a Tally Table. I have included a bunch of different date functions you could use as examples if you are using this a reference table, which is advised as opposed to creating on the fly. If you are creating a static table though, you are best just creating it the once and manually collating the lists of special dates to make sure they are correct:

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;

Output - Note the FiscalPeriodEndDate flag for your financial periods:

+------------+---------------------+---------+------------+----------+-----------+---------+----------------+-----------------+
|  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     |                     |         |            |          |           |         |                |                 |
+------------+---------------------+---------+------------+----------+-----------+---------+----------------+-----------------+

相关问题