Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Script to create Fiscal Calendar Table

Posted on 2011-02-24
Medium Priority
3,775 Views
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
0
Question by:JohnJMA
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 15

Assisted Solution

derekkromm earned 2000 total points
ID: 34976724
Do you only need the fiscal year? Or do you need additional fiscal columns on there as well?

If the former, the easiest way would be to determine the start date for each year, store them in a temp table, and populate from there.

So something along the lines of:

declare @fiscal table (fiscalyear int, startdate datetime, enddate datetime)
insert into @fiscal (fiscalyear, startdate)
select year, date from calendartable c where dayofweek=sunday and weeknumber = (select min(weeknumber) from calendertable c1 where dayofweek=sunday and year=c.year)

update f set enddate=f1.startdate-1 from @fiscal f inner join @fiscal f1 on f.fiscalyear = f1.fiscalyear + 1

update c set fiscalyear=f.fiscalyear from calendartable c inner join @fiscal f on c.date between f.startdate and f.enddate
0

LVL 15

Expert Comment

ID: 34977190
DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
'Last Day of Previous Month'
UNION
'First Day of Current Month' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION
'Last Day of Current Month'
UNION
'First Day of Next Month'
0

Author Comment

ID: 34979119
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,
0

Author Comment

ID: 34979130
Derekkromm, sorry for the typo in the previous post.
0

LVL 18

Expert Comment

ID: 34982923
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
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
return @Datefiscal
End
``````
0

Author Comment

ID: 34989448
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.
0

LVL 15

Accepted Solution

derekkromm earned 2000 total points
ID: 34989758
John,

I think this gets you a lot of the way there. If there are additional columns you need, you should be able to derive them from the columns already calculated.

``````declare @Calendar table (
CalendarDate datetime,
CalendarDayOfYear as datepart(dy, CalendarDate),
CalendarDayOfWeek as datepart(dw, CalendarDate),
CalendarYear as datepart(yy, CalendarDate),
FiscalDayOfYear int,
FiscalMonth as
case
when FiscalDayOfYear between 1 and 28 then 1
when FiscalDayOfYear between 29 and 56 then 2
when FiscalDayOfYear between 57 and 91 then 3
when FiscalDayOfYear between 92 and 119 then 4
when FiscalDayOfYear between 120 and 147 then 5
when FiscalDayOfYear between 148 and 182 then 6
when FiscalDayOfYear between 183 and 210 then 7
when FiscalDayOfYear between 211 and 238 then 8
when FiscalDayOfYear between 239 and 273 then 9
when FiscalDayOfYear between 274 and 301 then 10
when FiscalDayOfYear between 300 and 329 then 11
else 12
end,
FiscalQuarter as
case
when FiscalDayOfYear between 1 and 91 then 1
when FiscalDayOfYear between 92 and 182 then 2
when FiscalDayOfYear between 183 and 273 then 3
else 4
end,
FiscalYear int
)

declare @i int
select @i = 36524

while @i < 45000
begin
insert into @Calendar (CalendarDate) select @i
select @i = @i + 1
end

declare @FiscalStartEnd table (
FiscalYear int,
StartDate datetime,
EndDate datetime
)

insert into @FiscalStartEnd (FiscalYear, StartDate)
select
CalendarYear, MIN(CalendarDate)
from
@Calendar
where
CalendarDayOfWeek = 1
group by
CalendarYear

update
f
set
EndDate = f1.StartDate - 1
from
@FiscalStartEnd f
inner join @FiscalStartEnd f1
on	f.FiscalYear = f1.FiscalYear - 1

update
c
set
FiscalDayOfYear = DATEDIFF(d, f.StartDate, c.CalendarDate) + 1,
FiscalYear = f.FiscalYear
from
@FiscalStartEnd f
inner join @Calendar c
on	c.CalendarDate between f.StartDate and f.EndDate

select * from @Calendar
``````
0

Author Closing Comment

ID: 35009966
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.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includâ€¦
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
###### Suggested Courses
Course of the Month11 days, 15 hours left to enroll