13 Period Calendar 4-4-5 Calendar T-SQL MSSQL

Question

I am trying to create a 13 period calendar in mssql but I am a bit stuck. I am not sure if my approach is the best way to achieve this. I have my base script which can be seen below:

Set DateFirst  1

Declare @Date1 date = '20180101' --startdate should always be start of 
financial year
Declare @Date2 date = '20181231' --enddate should always be start of 
financial year

SELECT * INTO #CalendarTable
FROM dbo.CalendarTable(@Date1,@Date2,0,0,0)c
DECLARE @StartDate datetime,@EndDate datetime

SELECT @StartDate=MIN(CASE WHEN [Day]='Monday' THEN [Date] ELSE NULL END),
@EndDate=MAX([Date])
FROM #CalendarTable

;With Period_CTE(PeriodNo,Start,[End])
AS
(SELECT 1,@StartDate,DATEADD(wk,4,@StartDate) -1
UNION ALL
SELECT PeriodNo+1,DATEADD(wk,4,Start),DATEADD(wk,4,[End])
FROM Period_CTE
WHERE DATEADD(wk,4,[End])< =@EndDate
OR PeriodNo+1 <=13
)
select * from Period_CTE

Which gives me this:

PeriodNo Start End 1 2018-01-01 00:00:00.000 2018-01-28 00:00:00.000 2 2018-01-29 00:00:00.000 2018-02-25 00:00:00.000 3 2018-02-26 00:00:00.000 2018-03-25 00:00:00.000 4 2018-03-26 00:00:00.000 2018-04-22 00:00:00.000 5 2018-04-23 00:00:00.000 2018-05-20 00:00:00.000 6 2018-05-21 00:00:00.000 2018-06-17 00:00:00.000 7 2018-06-18 00:00:00.000 2018-07-15 00:00:00.000 8 2018-07-16 00:00:00.000 2018-08-12 00:00:00.000 9 2018-08-13 00:00:00.000 2018-09-09 00:00:00.000 10 2018-09-10 00:00:00.000 2018-10-07 00:00:00.000 11 2018-10-08 00:00:00.000 2018-11-04 00:00:00.000 12 2018-11-05 00:00:00.000 2018-12-02 00:00:00.000 13 2018-12-03 00:00:00.000 2018-12-30 00:00:00.000

The result i am trying to get is

Even if I have to take a different approach I would not mind, as long as the result is the same as the above.

dbo.CalendarTable() is a function that returns the following results. I can share the code if desired.

You approached from the wrong end. Start with your DayOfWeek column, and then use functions/subselects to get the other columns. — Tab Alleman, Mar 19 '18 at 14:17
I don't get the Monday part? Where is dbo.CalendarTable coming from? — paparazzo, Mar 19 '18 at 15:22

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

I'd create a general number's table like suggested here and add a column Periode13.

The trick to get the tiling is the integer division:

DECLARE @PeriodeSize INT=28; --13 "moon-months" a 28 days

SELECT TOP 100 (ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1)/@PeriodeSize
FROM master..spt_values --just a table with many rows to show the principles

You can add this to an existing numbers table with a simple update statement.

UPDATE A fully working example (using the logic linked above)

DECLARE @RunningNumbers TABLE  (Number INT NOT NULL
                               ,CalendarDate DATE NOT NULL
                               ,CalendarYear INT NOT NULL
                               ,CalendarMonth INT NOT NULL
                               ,CalendarDay INT NOT NULL
                               ,CalendarWeek INT NOT NULL
                               ,CalendarYearDay INT NOT NULL
                               ,CalendarWeekDay INT NOT NULL);

DECLARE @CountEntries INT = 100000;
DECLARE @StartNumber INT = 0;


WITH E1(N) AS(SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)), --10 ^ 1
    E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
    E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
    E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 10 ^ 8 = 10,000,000 rows
    CteTally AS
    (
        SELECT TOP(ISNULL(@CountEntries,1000000)) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) -1 + ISNULL(@StartNumber,0) As Nmbr
        FROM E8
    )
INSERT INTO @RunningNumbers
SELECT CteTally.Nmbr,CalendarDate.d,CalendarExt.*
FROM CteTally
CROSS APPLY
(
    SELECT DATEADD(DAY,CteTally.Nmbr,{ts'2018-01-01 00:00:00'})
) AS CalendarDate(d)
CROSS APPLY
(
    SELECT YEAR(CalendarDate.d) AS CalendarYear
          ,MONTH(CalendarDate.d) AS CalendarMonth
          ,DAY(CalendarDate.d) AS CalendarDay
          ,DATEPART(WEEK,CalendarDate.d) AS CalendarWeek
          ,DATEPART(DAYOFYEAR,CalendarDate.d) AS CalendarYearDay
          ,DATEPART(WEEKDAY,CalendarDate.d) AS CalendarWeekDay
) AS CalendarExt;

--The mockup table from above is now filled and can be queried

 WITH AddPeriode AS
(
    SELECT     Number/28 +1 AS PeriodNumber
              ,CalendarDate
              ,CalendarWeek
              ,r.CalendarDay 
              ,r.CalendarMonth 
              ,r.CalendarWeekDay 
              ,r.CalendarYear 
              ,r.CalendarYearDay 
    FROM @RunningNumbers AS r
)
SELECT TOP 100 p.*
          ,(SELECT MIN(CalendarDate) FROM AddPeriode AS x WHERE x.PeriodNumber=p.PeriodNumber) AS [Start]
          ,(SELECT MAX(CalendarDate) FROM AddPeriode AS x WHERE x.PeriodNumber=p.PeriodNumber) AS [End]
          ,(SELECT MIN(CalendarDate) FROM AddPeriode AS x WHERE x.PeriodNumber=p.PeriodNumber AND x.CalendarWeek=p.CalendarWeek) AS [wkStart]
          ,(SELECT MAX(CalendarDate) FROM AddPeriode AS x WHERE x.PeriodNumber=p.PeriodNumber AND x.CalendarWeek=p.CalendarWeek) AS [wkEnd]
          ,(ROW_NUMBER() OVER(PARTITION BY PeriodNumber ORDER BY CalendarDate)-1)/7+1 AS WeekOfPeriode
FROM AddPeriode AS p
ORDER BY CalendarDate

Try it out...

Hint: Do not use a `VIEW` or `iTVF` for this.

This is non-changing data and much better placed in a physically stored table with appropriate indexes.

Thanks Shnugo but I am not sure how exactly this helps me. Can you show a example of my result set using your method? The columns which are posing a problem for me are wkStartDate, wkEndDate, weekofperiod and weekofyear. If I can somehow determine wkStartDate and wkEndDate. I think I should be able to use window functions to get the other two. — Castell James, Mar 19 '18 at 15:15
this really helps. Thanks you. There is however one problem. I have been trying to modify this script to output 13 periods instead of 14 . Currently this is spitting out 14 period upward instead of 13 period aka 4-4-5 calendar. This is way trickery than I expected. — Castell James, Mar 25 '18 at 13:32
@CastellJames I must admit I had nothing to do with this kind of calendar before. My advise was: Use a script like the one above to create a calendar as close as possible. Copy this to Excel and modify it manually. Then copy it back into the physical table. Create a physically stored list for the next 20 years. This will take some hours - but it will take longer, to create a fully-magic-special-case-aware-wonder-script... — Shnugo, Mar 25 '18 at 15:18
@CastellJames One hint about the *14 periode upward*. Just use the **modulo operator** (like `(Periode-1) % 13`). This will start to count from 0 to 12 over and over... — Shnugo, Mar 25 '18 at 15:21
Thanks for your assistance with this. you have been a big help. — Castell James, Mar 25 '18 at 15:51
@CastellJames now you've unaccepted this again... Obviously you are still not happy with the existing answers. My suggestion was to start a new question, where you try to state your issue more specifically. This question will not get many visits anymore.Whether you close this (with acceptance) or not is up to you of course... If you still need help here, you'd have to state some further details... — Shnugo, Mar 28 '18 at 10:21
I get your point I have been working on another solution using snippets from your code. I have made good progress on the new solution however I have hit a road block. I will post a new question — Castell James, Apr 11 '18 at 19:57

score 0 · Answer 2 · answered Mar 19 '18 at 20:48

Not abundantly sure external links are accepted here, but I wrote an article that pulls of a 5-4-4 'Crop Year' fiscal year with all the code. Feel free to use all the code in these articles.

SQL Server Calendar Table

SQL Server Calendar Table: Fiscal Years

13 Period Calendar 4-4-5 Calendar T-SQL MSSQL

2 Answers2

UPDATE A fully working example (using the logic linked above)

Hint: Do not use a VIEW or iTVF for this.

Hint: Do not use a `VIEW` or `iTVF` for this.