May 8, 2012 at 10:06 am
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 53th 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
May 8, 2012 at 12:04 pm
Try this:
declare @CalendarStartDate datetime
declare @CalendarEndDate datetime
select @CalendarStartDate = '04/03/2006'
select @CalendarEndDate = '12/31/2018'
;WITH NumOfYears ( YrNum , DayCount ) AS
(
SELECT T.N , CASE WHEN T.N % 5 = 0 THEN 365 ELSE 364 END
FROM dbo.Tally T
WHERE T.N BETWEEN 1 AND 50
)
,DaysInEachYear (YrNum , Dt) AS
(
SELECT NoY.YrNum
,DATEADD( DD, (ROW_NUMBER() OVER (ORDER BY NoY.YrNum , T.N ) -1) , @CalendarStartDate)
FROM NumOfYears NoY
CROSS JOIN dbo.Tally T
WHERE T.N <= NoY.DayCount
AND T.N >= 1
)
SELECT D.YrNum ,D.Dt
FROM DaysInEachYear D
WHERE D.Dt <= @CalendarEndDate
Uses the same tally table 🙂
May 8, 2012 at 12:36 pm
The key is to understand that there aren't 52 weeks in your year, but rather 52.2 weeks rounded down (truncated).
Try the following
MyCount = Round(datediff(day, @CalendarStartDate, CalendarDate) / (7 * 52.2 ), 0, 1) + 1
Or if it makes more sense to think of it as 13.05 months.
MyCount = Round(datediff(day, @CalendarStartDate, CalendarDate) / (7 * 4 * 13.05 ), 0, 1) + 1
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 8, 2012 at 1:09 pm
That's fantastic, thanks! Works a treat.
Just changed: CASE WHEN T.N % 5 = 0 THEN 364 + 7 ELSE 364 END
so that it adds on an extra week each 5th year.
May 8, 2012 at 2:57 pm
Personally, rather than regenerate it like that for every query, I'd take the output and put it into a Date Dimension table
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply