Okay its not pretty!!! but for anyone wanting to create a fiscal calendar table for the next few years this might be worth a look.
function ( big Thanks To RyanEK) for pointing me the right direction as this has proved to be a very useful function.
Okay so
fiscal year starts at 27 and uses default of Sunday being the first day.
function
CREATE function FiscalWeek (@startMonth varchar(2), @myDate datetime)
returns int
as
begin
declare @firstWeek datetime
declare @weekNum int
declare @year int
set @year = datepart(year, @myDate)+1
--Get 4th day of month of next year, this will always be in week 1
set @firstWeek = convert(datetime, str(@year)+@startMonth+'27', 102)
--Retreat to beginning of week
set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek)
while @myDate < @firstWeek --Repeat the above steps but for previous year
begin
set @year = @year - 1
set @firstWeek = convert(datetime, str(@year)+@startMonth+'27', 102)
set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek)
end
set @weekNum = (@year*100)+((datediff(day, @firstweek, @myDate)/7)+1)
return @weekNum
end
selection
WITH calendar AS
(
SELECT cast('2009-04-26' as datetime) Date
UNION ALL
SELECT Date + 1
FROM calendar
WHERE Date + 1 < '2010-04-25'
)
--insert into i_calendar
SELECT '1'+ convert(varchar,date,12) as caldate,
Convert (varchar, date, 103) as date_desc,
Date,
DATENAME (weekday, date) AS day,
dbo.FiscalWeek('04',date)%100 Fiscal_week,
dbo.FiscalWeek('04',date -7)%100 LW_fiscal_week,
Substring (CAST(dbo.FiscalWeek('04',date -7) as Varchar), 1,4) as LW_Fiscal_Year_start,
'1'+ convert(varchar,date,12)-7 as lw_date,
Fiscal_period =
CASE
WHEN dbo.FiscalWeek('04',date)%100 <= 4 THEN 1
WHEN dbo.FiscalWeek('04',date)%100 <= 8 THEN 2
WHEN dbo.FiscalWeek('04',date)%100 <= 13 THEN 3
WHEN dbo.FiscalWeek('04',date)%100 <= 17 THEN 4
WHEN dbo.FiscalWeek('04',date)%100 <= 21 THEN 5
WHEN dbo.FiscalWeek('04',date)%100 <= 26 THEN 6
WHEN dbo.FiscalWeek('04',date)%100 <= 30 THEN 7
WHEN dbo.FiscalWeek('04',date)%100 <= 34 THEN 8
WHEN dbo.FiscalWeek('04',date)%100 <= 39 THEN 9
WHEN dbo.FiscalWeek('04',date)%100 <= 43 THEN 10
WHEN dbo.FiscalWeek('04',date)%100 <= 47 THEN 11
Else 12
END,
Fiscal_period_name =
CASE
WHEN dbo.FiscalWeek('04',date)%100 <= 4 THEN 'May'
WHEN dbo.FiscalWeek('04',date)%100 <= 8 THEN 'Jun'
WHEN dbo.FiscalWeek('04',date)%100 <= 13 THEN 'Jul'
WHEN dbo.FiscalWeek('04',date)%100 <= 17 THEN 'Aug'
WHEN dbo.FiscalWeek('04',date)%100 <= 21 THEN 'Sep'
WHEN dbo.FiscalWeek('04',date)%100 <= 26 THEN 'Oct'
WHEN dbo.FiscalWeek('04',date)%100 <= 30 THEN 'Nov'
WHEN dbo.FiscalWeek('04',date)%100 <= 34 THEN 'Dec'
WHEN dbo.FiscalWeek('04',date)%100 <= 39 THEN 'Jan'
WHEN dbo.FiscalWeek('04',date)%100 <= 43 THEN 'Feb'
WHEN dbo.FiscalWeek('04',date)%100 <= 47 THEN 'Mar'
Else 'Apr'
END ,
--DATEPART(Quarter ,Date) AS Quarter,
Fiscal_quarter =
CASE
WHEN dbo.FiscalWeek('04',date)%100 <= 13 THEN 1
WHEN dbo.FiscalWeek('04',date)%100 <= 26 THEN 2
WHEN dbo.FiscalWeek('04',date)%100 <= 39 THEN 3
ELSE 4
END,
Fiscal_quarter_name =
CASE
WHEN dbo.FiscalWeek('04',date)%100 <= 13 THEN 'Q1'
WHEN dbo.FiscalWeek('04',date)%100 <= 26 THEN 'Q2'
WHEN dbo.FiscalWeek('04',date)%100 <= 39 THEN 'Q3'
ELSE 'Q4'
END,
Substring (CAST(dbo.FiscalWeek('04',date) as Varchar), 1,4) as fiscal_year_start,
Substring (CAST(dbo.FiscalWeek('04',date) as Varchar), 3,2)
+ ' '
+ Cast ((Substring (CAST(dbo.FiscalWeek('04',getdate()) as varchar), 3,2)) +2 as Varchar) as Fiscal_year,
DateDiff(Day, -2, date) as dateno,--,
DATENAME( month,Date) AS MonthName,
DATEPART(m ,Date) as MonthNo
FROM calendar
OPTION (MAXRECURSION 0)
my assumpitons are that both fiscal period and quarter are static and fiscal period operates on i.e. 4 4 5 in terms of a period and three periods to a quarter the only way I could think of how to do this was to use CASE thoughits a bit messy.
However if anyone else has a cleaner way of doing it I would be more than willing to try it.
Many Thanks to all
