locked
13 X 4 Financial Year RRS feed

  • 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

Answers

  • 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/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Proposed as answer by Naomi N Tuesday, May 8, 2012 8:17 PM
    • Edited by Kalman Toth Sunday, October 7, 2012 9:26 PM
    • Marked as answer by Kalman Toth 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 johnqflorida Wednesday, May 9, 2012 10:30 AM Fixed typo
    • Proposed as answer by Naomi N Wednesday, May 9, 2012 7:05 PM
    • Marked as answer by Iric Wen 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/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Proposed as answer by Naomi N Tuesday, May 8, 2012 8:17 PM
    • Edited by Kalman Toth Sunday, October 7, 2012 9:26 PM
    • Marked as answer by Kalman Toth 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.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth 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.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth 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....


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth 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 johnqflorida Wednesday, May 9, 2012 10:30 AM Fixed typo
    • Proposed as answer by Naomi N Wednesday, May 9, 2012 7:05 PM
    • Marked as answer by Iric Wen Wednesday, May 16, 2012 1:18 AM
    Wednesday, May 9, 2012 10:19 AM