I saw a question on dbforums.com asking for how to determine the financial quarter value for a given date. I think the solution below will give one a good start on that:
if object_id('dbo.fn__MonthWithOffset') is not null
drop function dbo.fn__MonthWithOffset
go
create function dbo.fn__MonthWithOffset (
@monthNumber tinyint
,@monthOffset tinyint
,@operation char(1) /* + or - */ ) returns tinyint
as begin
declare @result tinyint
if @operation not in ('+','-')
return (null)
if @monthNumber not between 1 and 12
return (null)
if @operation = '+' begin
if (cast(@monthNumber as int) + cast(@monthOffset as int)) > 12
set @result = cast((cast(@monthNumber as int) + cast(@monthOffset as int)) % 12 as tinyint)
else
set @result = @monthNumber + @monthOffset
end else if @operation = '-' begin
if @monthOffset > 12
set @monthOffset = cast((@monthOffset % 12) as tinyint)
if (cast(@monthNumber as int) - @monthOffset) <= 0
set @result = 12 - (@monthOffset - @monthNumber)
else
set @result = @monthNumber - @monthOffset
end
return (@result)
end
go
if object_id('dbo.fn__FinancialQuarter') is not null
drop function dbo.fn__FinancialQuarter
go
create function dbo.fn__FinancialQuarter (
@date datetime
,@firstMonth tinyint ) returns tinyint
as begin
return (
select
case datepart(month, @date)
when dbo.fn__MonthWithOffset(@firstMonth, 0, '+') then 1
when dbo.fn__MonthWithOffset(@firstMonth, 01, '+') then 1
when dbo.fn__MonthWithOffset(@firstMonth, 02, '+') then 1
when dbo.fn__MonthWithOffset(@firstMonth, 03, '+') then 2
when dbo.fn__MonthWithOffset(@firstMonth, 04, '+') then 2
when dbo.fn__MonthWithOffset(@firstMonth, 05, '+') then 2
when dbo.fn__MonthWithOffset(@firstMonth, 06, '+') then 3
when dbo.fn__MonthWithOffset(@firstMonth, 07, '+') then 3
when dbo.fn__MonthWithOffset(@firstMonth, 08, '+') then 3
when dbo.fn__MonthWithOffset(@firstMonth, 09, '+') then 4
when dbo.fn__MonthWithOffset(@firstMonth, 10, '+') then 4
when dbo.fn__MonthWithOffset(@firstMonth, 11, '+') then 4
end
)
end
go
select dbo.fn__FinancialQuarter ('11/01/2009', 11)
No comments:
Post a Comment