Solved

Script to create Fiscal Calendar Table

Posted on 2011-02-24
8
2,967 Views
Last Modified: 2012-05-11
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
Comment
Question by:JohnJMA
8 Comments
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 500 total points
Comment Utility
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

by:Aaron Shilo
Comment Utility
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'
0
 

Author Comment

by:JohnJMA
Comment Utility
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

by:JohnJMA
Comment Utility
Derekkromm, sorry for the typo in the previous post.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 18

Expert Comment

by:sventhan
Comment Utility
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

0
 

Author Comment

by:JohnJMA
Comment Utility
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

by:
derekkromm earned 500 total points
Comment Utility
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

Open in new window

0
 

Author Closing Comment

by:JohnJMA
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Authentication and Win NT Authentication Issues 20 38
Need Counts 11 40
Getting certain data from a string 1 22
Sql query 34 16
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now