Please, help to create a Fiscal and Calendar table with the following conditions:

The table and the first fiscal year start from 01/31/2011

The end date could be 12/31/2020 (not that important as it could be a smaller date)

All the next fiscal years should start 52 weeks after on the following Monday.

I’m trying to modify the following code that is calculating the beginning of the fiscal year with 52 weeks ahead but with following Monday only for the first next Fiscal Year.

Thanks

DECLARE

@StartDate smalldatetime

= '01/31/2011'

--First Calendar date to include in table

DECLARE

@EndDate smalldatetime

= '12/31/2020'

--Last calendar date to include in the table

declare

@FiscalYearBegin_First smalldatetime

= '1/31/2011'

DECLARE

@Cycle INT

= 52

SET

DATEFIRST 7

;WITH

--This secton generates the number table

E00(N)

AS (SELECT 1

UNION ALL

SELECT 1),

E02(N)

AS (SELECT 1

FROM E00

a, E00

b),

E04(N)

AS (SELECT 1

FROM E02

a, E02

b),

E08(N)

AS (SELECT 1

FROM E04

a, E04

b),

E16(N)

AS (SELECT 1

FROM E08

a, E08

b),

E32(N)

AS (SELECT 1

FROM E16

a, E16

b),

cteTally(N)

AS (SELECT

ROW_NUMBER()

OVER (ORDER

BY N)

FROM E32),

--This CTE generates a list of calendar dates

CalendarBase

as (

SELECT

DateKey =

n

, CalendarDate

= DATEADD(day,

n -1,

@StartDate )

case when n

= 1 then

@FiscalYearBegin_First

else

case when

datepart(dw,DATEADD(DAY,

n -1

, @StartDate ))

= 2 then

dateadd(week,CAST(ABS(DATEDIFF(WEEK,DATEADD(day,

n -1

, @StartDate ),@StartDate))[email protected]

AS INT)

* @Cycle,@FiscalYearBegin_First)

else

dateadd(week,

datediff(week, 0,

dateadd(week,CAST(ABS(DATEDIFF(WEEK,DATEADD(day,

n - 1,

@StartDate ),@StartDate))[email protected]

AS INT)

* @Cycle,@FiscalYearBegin_First)), 7)

end

end FiscalYearBegin

FROM cteTally

WHERE

N <=

DATEDIFF(day,

@StartDate ,

@EndDate +1)

--Finally, use the list of calendar dates to fill the date dimension

SELECT

DateKey

, IsoDate

= CONVERT(char(8),

CalendarDate, 112)

, CalendarDate

, CalendarYear

= YEAR(CalendarDate)

, CalendarQuarter

= (DATEPART(QUARTER,CalendarDate)

, CalendarMonth

= MONTH(CalendarDate)

, CalendarDay

= DATEPART(DAY,

CalendarDate)

, DayofWk

= DATEPART(Dw,

CalendarDate)

, CalendarWeekOfMonth

= DATEDIFF(week,

DATEADD(day,1,

CalendarDate-DAY(CalendarDate)

+ 1)

-1, CalendarDate)

+1

, WeekofYr

= DATEPART(WEEK,

CalendarDate)

, DayofYr

= DATEPART(DAYOFYEAR,

CalendarDate)

, NameMonth

= DATENAME(Month,

CalendarDate)

, NameDay

= DATENAME

(Weekday,CalendarDate

, FiscalYear

= YEAR(FiscalYearBegin)

, FiscalMonth

= DATEDIFF(

MONTH,

FiscalYearBegin,

CalendarDate) + 1

, FiscalQuarter

= DATEDIFF(

QUARTER,

FiscalYearBegin,

CalendarDate) + 1

, FiscalWeek

= DATEDIFF(

WEEK,

FiscalYearBegin,

CalendarDate) + 1

, FiscalDay

= DATEDIFF(

day,

FiscalYearBegin,

CalendarDate) + 1

FROM

CalendarBase