[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4056
  • Last Modified:

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  
0
JohnJMA
Asked:
JohnJMA
2 Solutions
 
derekkrommCommented:
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
 
Aaron ShiloChief Database ArchitectCommented:
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
 
JohnJMAAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
JohnJMAAuthor Commented:
Derekkromm, sorry for the typo in the previous post.
0
 
sventhanCommented:
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
 
JohnJMAAuthor Commented:
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
 
derekkrommCommented:
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
 
JohnJMAAuthor Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now