There is a script to generate the fiscal calendar.
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
 |
This email has been checked for viruses by Avast antivirus software.
www.avast.com |
No comments:
Post a Comment