Solved

Script to create Fiscal Calendar Table

Posted on 2011-02-24
8
3,094 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
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

by:Aaron Shilo
ID: 34977190
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
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

by:JohnJMA
ID: 34979130
Derekkromm, sorry for the typo in the previous post.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 18

Expert Comment

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

by:
derekkromm earned 500 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

Open in new window

0
 

Author Closing Comment

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculate values in an array 18 35
Using Aggregate Functions to Count 3 34
Extract string portion 2 14
MSSQL: Substring and Charindex error 7 19
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

895 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

15 Experts available now in Live!

Get 1:1 Help Now