# 13 X 4 Financial Year

• ### Question

•  I've got this far to generate financial years. From an arbitrary start date it counts each financial year as 364 days = (13 X 4 X 7). ```declare @MyCalendar table ( CalendarDate datetime, MyCount int ) declare @CalendarStartDate datetime declare @CalendarEndDate datetime select @CalendarStartDate = '04/03/2006' select @CalendarEndDate = '12/31/2018' insert @MyCalendar ( CalendarDate, MyCount ) select CalendarDate, MyCount = (datediff(day, @CalendarStartDate, CalendarDate) / (7 * 4 * 13)) + 1 from ( select CalendarDate = dateadd(day, N, @CalendarStartDate) from Tally where dateadd(day, N, @CalendarStartDate) <= @CalendarEndDate ) Calendar where CalendarDate between @CalendarStartDate and @CalendarEndDate --Test select * from @MyCalendar select MyCount, count(*) from @MyCalendar group by MyCount order by MyCount``` Just realised I have used a Tally table - one column N going from 0 to at least 5000. The rule I've been given: Every 5th year will contain an extra 53rd week, to make up for the loss of one (or two if leap year) day each year. I've tried using a CASE but failing on the logic. Thanks
Tuesday, May 8, 2012 5:41 PM

• I suggest you submit the 13 month calendar to Washington (which department?) for consideration.

The new (13th) month can be called: UNDECIMBER (undecim is 11 in latin).

The last week of the year picks up the extra day(s): EARTHDAY, STARDAY. So the last week is 8 or 9 days.

You can do the leap year check (STARDAY in leapyears extra vacation day, so is MOONDAY each year) the following way:

```WITH ctesequence ( YEAR)
AS (SELECT 1979
UNION ALL
SELECT YEAR + 1
FROM   ctesequence
WHERE  YEAR < 2081)
SELECT YEAR,
LEAP=CASE
WHEN YEAR % 4 = 0 THEN 1
ELSE 0
END
FROM   ctesequence
OPTION ( maxrecursion 0);

go
/*
YEAR	LEAP
1979	 0
1980	 1
1981	 0
1982	 0
1983	 0
1984	 1
1985	 0
1986	 0
1987	 0
1988	 1
1989	 0
1990	 0
1991	 0 ...*/```

Related blog post:

http://www.sqlusa.com/bestpractices2005/sequence/

• Proposed as answer by Tuesday, May 8, 2012 8:17 PM
• Edited by Sunday, October 7, 2012 9:26 PM
• Marked as answer by Sunday, October 7, 2012 9:26 PM
Tuesday, May 8, 2012 8:10 PM
• Ok... I'm sorry I was a smart aleck.  I apologize. Will this help me be forgiven?

```Declare @SeqNo Table (N int primary key)
Insert @SeqNo (N)
Select top 371  Row_Number() over(Order by @@ServerName) as RowNo
from SYS.MESSAGES  /* Just one of many ways to derive sequential lists */

Declare @DateTable Table (SeqNo int, YearNo int, IsLeap5Year int, DaysThisYear int, DayNo int)

Insert @DateTable
Select Row_Number() over(Order by @@ServerName) as SeqNo, *
From (Select top 100 N as YearNo from @SeqNo) DTYear
Cross Apply (Select Case YearNo % 5 when 0 then 1 else 0 End as IsLeapYear) CALeap
Cross Apply (Select Case IsLeapYear when 1 then 371 else 364 End as DaysThisYear) CADaysThisYear
Cross Apply (Select top 371 N as DayNo from @SeqNo where N <= DaysThisYear) as DTDay

Select YearNo, DateItem   /* Change to Select * to see the inner workings */
from @DateTable
Cross Apply (Select DateAdd(day, SeqNo, '2006-04-03') as DateItem) as CADate /* Consider casting as DATE, if SQL 2008 or higher */
Where DateItem between '2006-04-03' and '2018-12-31'```

Partly because of the method I chose for computing the sequence numbers, and partly because of using Row_Number() heavily, the process of creating the table variables is slowish (not terribly slow, and by no means problematic enough to bog down a server or anything, just not a speed to brag about).   The logic is sound, but as you'll hear from many sources, "Create a calendar table" means create a permanent table with the computed columns, etc., (and even consider just populating it with hundreds of years), and reference the table in queries later on.

• Edited by Wednesday, May 9, 2012 10:30 AM Fixed typo
• Proposed as answer by Wednesday, May 9, 2012 7:05 PM
• Marked as answer by Wednesday, May 16, 2012 1:18 AM
Wednesday, May 9, 2012 10:19 AM

### All replies

• I suggest you submit the 13 month calendar to Washington (which department?) for consideration.

The new (13th) month can be called: UNDECIMBER (undecim is 11 in latin).

The last week of the year picks up the extra day(s): EARTHDAY, STARDAY. So the last week is 8 or 9 days.

You can do the leap year check (STARDAY in leapyears extra vacation day, so is MOONDAY each year) the following way:

```WITH ctesequence ( YEAR)
AS (SELECT 1979
UNION ALL
SELECT YEAR + 1
FROM   ctesequence
WHERE  YEAR < 2081)
SELECT YEAR,
LEAP=CASE
WHEN YEAR % 4 = 0 THEN 1
ELSE 0
END
FROM   ctesequence
OPTION ( maxrecursion 0);

go
/*
YEAR	LEAP
1979	 0
1980	 1
1981	 0
1982	 0
1983	 0
1984	 1
1985	 0
1986	 0
1987	 0
1988	 1
1989	 0
1990	 0
1991	 0 ...*/```

Related blog post:

http://www.sqlusa.com/bestpractices2005/sequence/

• Proposed as answer by Tuesday, May 8, 2012 8:17 PM
• Edited by Sunday, October 7, 2012 9:26 PM
• Marked as answer by Sunday, October 7, 2012 9:26 PM
Tuesday, May 8, 2012 8:10 PM
• Don't mock! (Well, either that, or hurry up and mock!)  This is computing the end of the world!

(Mayan calendar, homework assignment, or both... )  :-)

Tuesday, May 8, 2012 8:21 PM
• So you've never heard of 13X4 financial accounting periods? I'm working with a UK company that does just this and its not that uncommon.
Tuesday, May 8, 2012 9:35 PM
• Oh dear. Go away.
Tuesday, May 8, 2012 9:38 PM
• So you've never heard of 13X4 financial accounting periods? I'm working with a UK company that does just this and its not that uncommon.

I am afraid, I never heard of it.

But 13 months x 4 weeks ( extra long week at year end) makes lots of sense to me.

I find the random 28, 29, 30, 31 days in a month most illogical.

• Edited by Sunday, October 7, 2012 9:27 PM
Tuesday, May 8, 2012 10:00 PM
• The last week of the year picks up the extra day(s): MOONDAY, STARDAY. So the last week is 8 or 9 days.

Hey Kalman. Starday was a good one, but the Moon already has one day in the week! But we could call that extra day for leap years for Earth Day - an upgrade from Earth Hour, so the lights will be off all day!

Sorry, Zapouk, I'm running out of time to look at this one tonight. I hope someone else can help you.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Tuesday, May 8, 2012 10:17 PM
• Hi Erland,

You mean Monday? You are right, it does sound like Moonday.

So the extra days in the 13 months x 4 weeks calendar: STARDAY, EARTHDAY at year end.

• Edited by Sunday, October 7, 2012 9:27 PM
Tuesday, May 8, 2012 10:24 PM
• Don't mock! (Well, either that, or hurry up and mock!)  This is computing the end of the world!

(Mayan calendar, homework assignment, or both... )  :-)

No it's not the Mayan Calendar or anyone else's calendar, it's financial accounting periods. They are used in the UK, perhaps not in the US. I'm not doing this by choice, it's work, I would have though this was obvious from the sql script I provided.

I have found this forum very useful in the past but I may go elsewhere now.

Don't you think "homework assignment" is insulting? Perhaps an apology is in order.

Wednesday, May 9, 2012 8:04 AM
• You mean Monday? You are right, it does sound like Moonday.

And yes, it is the day of the moon. Lunedí in Italian and similar in Romance languages. Maanantai in Finnish, and yes it's the same. In Icelandic they did away with the names from heathen Gods, so Tuesday to Friday is (translated to English) is Second-day Midweek, Fourth-day and Fifthday.
But Sunday and Monday are still the same. (Saturday is Bathing-day in Icelandic - same as in Swedish.)

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Wednesday, May 9, 2012 8:11 AM
•  I'm not doing this by choice, it's work, I would have though this was obvious from the sql script I provided.

Well, everybody is so used to the Gregorian (since Pope Gregory - 1582) calendar, that the logic introduced by the the 13 months x 4 weeks (+ Starday, Earthday - leap)  logical calendar comes as a shocker.

What day the year started in 2000? Well in the 13x4 calendar every year and each month starts on Monday (or Sunday if you prefer).

Financial performance evaluation is also more accurate in 13x4 calendar since each month has 28 days.

Wonder if we should turn to the Pope, UN or Washington for a new calendar? Maybe Washington... Congress just changed the daylight savings times....

• Edited by Sunday, October 7, 2012 9:29 PM
Wednesday, May 9, 2012 10:01 AM
• Ok... I'm sorry I was a smart aleck.  I apologize. Will this help me be forgiven?

```Declare @SeqNo Table (N int primary key)
Insert @SeqNo (N)
Select top 371  Row_Number() over(Order by @@ServerName) as RowNo
from SYS.MESSAGES  /* Just one of many ways to derive sequential lists */

Declare @DateTable Table (SeqNo int, YearNo int, IsLeap5Year int, DaysThisYear int, DayNo int)

Insert @DateTable
Select Row_Number() over(Order by @@ServerName) as SeqNo, *
From (Select top 100 N as YearNo from @SeqNo) DTYear
Cross Apply (Select Case YearNo % 5 when 0 then 1 else 0 End as IsLeapYear) CALeap
Cross Apply (Select Case IsLeapYear when 1 then 371 else 364 End as DaysThisYear) CADaysThisYear
Cross Apply (Select top 371 N as DayNo from @SeqNo where N <= DaysThisYear) as DTDay

Select YearNo, DateItem   /* Change to Select * to see the inner workings */
from @DateTable
Cross Apply (Select DateAdd(day, SeqNo, '2006-04-03') as DateItem) as CADate /* Consider casting as DATE, if SQL 2008 or higher */
Where DateItem between '2006-04-03' and '2018-12-31'```

Partly because of the method I chose for computing the sequence numbers, and partly because of using Row_Number() heavily, the process of creating the table variables is slowish (not terribly slow, and by no means problematic enough to bog down a server or anything, just not a speed to brag about).   The logic is sound, but as you'll hear from many sources, "Create a calendar table" means create a permanent table with the computed columns, etc., (and even consider just populating it with hundreds of years), and reference the table in queries later on.

• Edited by Wednesday, May 9, 2012 10:30 AM Fixed typo
• Proposed as answer by Wednesday, May 9, 2012 7:05 PM
• Marked as answer by Wednesday, May 16, 2012 1:18 AM
Wednesday, May 9, 2012 10:19 AM