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.
1条答案
按热度按时间9bfwbjaz1#
I have ignored your
Sales Flash
andFinal 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 couldjoin
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 differentdate
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:Output - Note the
FiscalPeriodEndDate
flag for your financial periods: