asked on

Script to create Fiscal Calendar Table

Hello,

I have figured out how to create a table that holds calendar dates, day, day of week, week number, quarter, year, etc. What I need to do is to ad fiscal year columns to the calendar table. The problem is that our fiscal year starts the first Sunday of January, i.e. 2009 fiscal year is 1/4/2009 - 1/2/2010, 2010 fiscal year 1/3/2010 - 1/1/2011, and 2011 fiscal year 1/2/2011 - 12/31/201. We also run a 4-4-5 week periods. I need to be able to calculate all of the fiscal year information and put it in the calendar table. Any suggestions on how this can be done?

Thanks in advance for the help.

John

SOLUTION

derekkromm

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aaron Shilo

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,
'Last Day of Previous Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
'First Day of Current Month' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
'Last Day of Current Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,
'First Day of Next Month'

JohnJMA

ASKER

Kerekkromm, I need the fiscal year, fiscal quarter, fiscal day of year, day of week, fiscal month, fiscal week number and then a period number (this would be to put weeks together so January would be 4 weeks in a period, Feb would be 4 weeksin a period, March would be a 5 week period, etc. So for 2010 Jan 1, 2011 would have been the last day of the fiscal year 2010 and Jan 2, 2011 would be the first day of the fiscal year 2011, the first week, etc.

Does this make more sense?

Thanks,

JohnJMA

ASKER

Derekkromm, sorry for the typo in the previous post.

sventhan

http://www.sqlservercentral.com/scripts/T-SQL/71242/

-- To get the Fiscal start Date
create FUNCTION GetQuarterStartDate 
(
@YearandQuarter varchar(10)
)
RETURNS Date
AS
Begin 
Declare @DateFiscal Date
Select @YearandQuarter =
Case substring(@YearandQuarter,7,7)
when 1 then 
substring(@YearandQuarter,1,4) + '-01-01'
when 2 then
substring(@YearandQuarter,1,4) + '-04-01'
when 3 then
substring(@YearandQuarter,1,4) + '-07-01'
when 4 then
substring(@YearandQuarter,1,4) + '-10-01'
end
select @DateFiscal =DATEADD(qq,DATEDIFF(qq,0, @YearandQuarter) ,0)
set @DateFiscal = Dateadd(mm, -3,@DateFiscal)
return @Datefiscal
End


-- To get the Fiscal End Date
create FUNCTION GetQuarterEndDate 
(
@YearandQuarter varchar(10)
)
RETURNS Date
AS
Begin 
Declare @Datefiscal Date
Select @YearandQuarter =
Case substring(@YearandQuarter,7,7)
when 1 then 
substring(@YearandQuarter,1,4) + '-01-01'
when 2 then
substring(@YearandQuarter,1,4) + '-04-01'
when 3 then
substring(@YearandQuarter,1,4) + '-07-01'
when 4 then
substring(@YearandQuarter,1,4) + '-10-01'
end
select @DateFiscal = DATEADD(qq,DATEDIFF(qq,-1,@YearandQuarter),-1)
set @DateFiscal = Dateadd(mm, -3,@DateFiscal)
return @Datefiscal
End

Open in new window

JohnJMA

ASKER

sventhan, the problem is that the fiscal year starts on the first sunday of January and not at the beginning of any month let alone the beginning of a quarter. Our weeks run from Sunday to Saturday, for example the first state of this year starts 1/2/2011 and the end of the month is 1/29/2011, a 4 week period.

ASKER CERTIFIED SOLUTION

derekkromm

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JohnJMA

ASKER

Derekkromm, thanks for both solutions. It gets me close enough to the final solution that I can generate the other field values without any problem.