How to make a fiscal calendar in sql

%3CLINGO-SUB%20id%3D%22lingo-sub-1550242%22%20slang%3D%22en-US%22%3EHow%20to%20make%20a%20fiscal%20calendar%20in%20sql%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1550242%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20our%20server%2C%20we%20have%20a%20Dim.Calendar%20table%20which%20contains%2C%20fulldate%20(ex%3A%202020-07-28)%2C%20dateskey%20(20200728)%2C%20dayoftheweek%20(sunday%3D1)%20%2Cfiscal%20week%20number%20(vary%20between%201%20and%205)%2C%20fiscalweekstartdate%20(2020-01-01%2C%20firstday%20of%20the%20year%2C%20usually%20it%20is%20a%20sunday)%2C%20fiscalweekenddate%20(2020-01-04%2C%20a%20saturday)%2C%20fiscal%20period(ex%3B%20202001)%20%2C%20fiscalweekname(ex%3A%202020-01-w1%20until%202020-01-w5%20for%20January).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%2C%20our%20dim.calendar%20goes%20to%202025-12-31%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20someone%20can%20propose%20me%20a%20sql%20script%20to%20reproduce%20this%20dim.calendar%20table%20let's%20say%20from%202010%20to%202050%3CBR%20%2F%3EThe%20fiscal%20year%20start%20the%20first%20day%20of%20the%20year%2C%20end%20the%20last%20day%20of%20the%20year%20and%20we%20have%20the%20following%20number%20of%20fiscalweek%20per%20month%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ejan%205%3C%2FP%3E%3CP%3Efeb%204%3C%2FP%3E%3CP%3Emar%204%3C%2FP%3E%3CP%3Eapril%205%3C%2FP%3E%3CP%3Emay%204%3C%2FP%3E%3CP%3Ejune%204%3C%2FP%3E%3CP%3Ejuly%205%3C%2FP%3E%3CP%3Eaug%204%3C%2FP%3E%3CP%3Esept%204%3C%2FP%3E%3CP%3Eoct%205%3C%2FP%3E%3CP%3Enov%204%3C%2FP%3E%3CP%3Edec%204%3CBR%20%2F%3E%3CBR%20%2F%3Ethank%20in%20advance%20for%20your%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1550242%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hello,

 

On our server, we have a Dim.Calendar table which contains, fulldate (ex: 2020-07-28), dateskey (20200728), dayoftheweek (sunday=1) ,fiscal week number (vary between 1 and 5), fiscalweekstartdate (2020-01-01, firstday of the year, usually it is a sunday), fiscalweekenddate (2020-01-04, a saturday), fiscal period(ex; 202001) , fiscalweekname(ex: 2020-01-w1 until 2020-01-w5 for January).

 

Unfortunately, our dim.calendar goes to 2025-12-31

 

Does someone can propose me a sql script to reproduce this dim.calendar table let's say from 2010 to 2050
The fiscal year start the first day of the year, end the last day of the year and we have the following number of fiscalweek per month

 

jan 5

feb 4

mar 4

april 5

may 4

june 4

july 5

aug 4

sept 4

oct 5

nov 4

dec 4

thank in advance for your help

 

 

 

0 Replies