UFN_CALCQUARTER
Return
| Return Type |
|---|
| varchar(10) |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @InputDate | varchar(20) | IN | |
| @QuarterStartMonth | int | IN | |
| @QuarterOnly | int | IN |
Definition
Copy
create function [BBDW].[UFN_CALCQUARTER]
(@InputDate varchar(20),
@QuarterStartMonth int,
@QuarterOnly int = 0)
returns varchar(10)
as
begin
-- UDF which returns the Quarter Number and Year based on being passed a Data and Month when a fiscal year starts
-- Arguments @QuartOnly = 0 for Qn-YYYY format for this Qtr, 1 for Qn only format, 2 for Qtr YYYY only format
-- Arguments @QuartOnly = 3 for Qn-YYYY format for Next Qtr, 4 for Next Qn only format, 5 for Next Qtr YYYY only format
-- Arguments @QuartOnly = 6 for Qn-YYYY format for Next Qtr, 7 for Next Qn only format, 8 for Next Qtr YYYY only format
declare @FiscalMonth as varchar(2)
declare @FiscalQuarter as varchar(2)
declare @FiscalYear as varchar(4)
declare @FiscalQuarterYear as varchar(20)
declare @FiscalNextQuarter as varchar(2)
declare @FiscalNextYear as varchar(4)
declare @FiscalNextQuarterYear as varchar(20)
declare @FiscalPrevQuarter as varchar(2)
declare @FiscalPrevYear as varchar(4)
declare @FiscalPrevQuarterYear as varchar(20)
declare @ThisYear as varchar(4)
declare @NextYear as varchar(4)
declare @PrevYear as varchar(4)
declare @FiscalMonthNum as int
declare @NextQuarterStartMonth as int
declare @QuarterNum as int
declare @iPos as int
declare @FiscalQuarterMonth as int
set @iPos = patindex('%/%',@InputDate)
set @FiscalMonth = left(@InputDate,@iPos
- 1)
set @FiscalMonthNum = convert(int,@FiscalMonth)
set @NextQuarterStartMonth = @QuarterStartMonth
set @ThisYear = right(@InputDate,4)
set @NextYear = convert(varchar(4),convert(int,right(@InputDate,4))
+ 1)
set @PrevYear = convert(varchar(4),convert(int,right(@InputDate,4))
- 1)
if @QuarterStartMonth = 1
begin
set @QuarterNum = ((@FiscalMonthNum
+ 2)
/ 3)
set @FiscalQuarter = 'Q'
+ convert(varchar(1),@QuarterNum)
set @FiscalYear = @ThisYear
if @QuarterNum < 4
begin
set @FiscalNextYear = @ThisYear
set @FiscalNextQuarter = 'Q'
+ convert(varchar(1),@QuarterNum
+ 1)
end
if @QuarterNum = 4
begin
set @FiscalNextYear = @NextYear
set @FiscalNextQuarter = 'Q1'
end
if @QuarterNum = 1
begin
set @FiscalPrevYear = @PrevYear
set @FiscalPrevQuarter = 'Q4'
end
if @QuarterNum > 1
begin
set @FiscalPrevYear = @ThisYear
set @FiscalPrevQuarter = 'Q'
+ convert(varchar(1),@QuarterNum
- 1)
end
end
else
begin
if (@QuarterStartMonth = 1
and @FiscalMonthNum in (1,2,3))
or (@QuarterStartMonth = 2
and @FiscalMonthNum in (2,3,4))
or (@QuarterStartMonth = 3
and @FiscalMonthNum in (3,4,5))
or (@QuarterStartMonth = 4
and @FiscalMonthNum in (4,5,6))
or (@QuarterStartMonth = 5
and @FiscalMonthNum in (5,6,7))
or (@QuarterStartMonth = 6
and @FiscalMonthNum in (6,7,8))
or (@QuarterStartMonth = 7
and @FiscalMonthNum in (7,8,9))
or (@QuarterStartMonth = 8
and @FiscalMonthNum in (8,9,10))
or (@QuarterStartMonth = 9
and @FiscalMonthNum in (9,10,11))
or (@QuarterStartMonth = 10
and @FiscalMonthNum in (10,11,12))
or (@QuarterStartMonth = 11
and @FiscalMonthNum in (11,12,1))
or (@QuarterStartMonth = 12
and @FiscalMonthNum in (12,1,2))
begin
------------------------------- Q1 ------------------------------------
set @FiscalPrevQuarter = 'Q4'
set @FiscalQuarter = 'Q1'
set @FiscalNextQuarter = 'Q2'
set @FiscalPrevYear = @ThisYear
set @FiscalYear = @NextYear
set @FiscalNextYear = @NextYear
set @FiscalQuarterMonth = @QuarterStartMonth
end
if (@QuarterStartMonth = 1
and @FiscalMonthNum in (4,5,6))
or (@QuarterStartMonth = 2
and @FiscalMonthNum in (5,6,7))
or (@QuarterStartMonth = 3
and @FiscalMonthNum in (6,7,8))
or (@QuarterStartMonth = 4
and @FiscalMonthNum in (7,8,9))
or (@QuarterStartMonth = 5
and @FiscalMonthNum in (8,9,10))
or (@QuarterStartMonth = 6
and @FiscalMonthNum in (9,10,11))
or (@QuarterStartMonth = 7
and @FiscalMonthNum in (10,11,12))
or (@QuarterStartMonth = 8
and @FiscalMonthNum in (11,12,1))
or (@QuarterStartMonth = 9
and @FiscalMonthNum in (12,1,2))
or (@QuarterStartMonth = 10
and @FiscalMonthNum in (1,2,3))
or (@QuarterStartMonth = 11
and @FiscalMonthNum in (2,3,4))
or (@QuarterStartMonth = 12
and @FiscalMonthNum in (3,4,5))
begin
------------------------------- Q2 ------------------------------------
set @FiscalPrevQuarter = 'Q1'
set @FiscalQuarter = 'Q2'
set @FiscalNextQuarter = 'Q3'
set @FiscalYear = @NextYear
set @FiscalNextYear = @NextYear
set @FiscalPrevYear = @NextYear
set @FiscalQuarterMonth = @QuarterStartMonth
+ 3
end
if (@QuarterStartMonth = 1
and @FiscalMonthNum in (7,8,9))
or (@QuarterStartMonth = 2
and @FiscalMonthNum in (8,9,10))
or (@QuarterStartMonth = 3
and @FiscalMonthNum in (9,10,11))
or (@QuarterStartMonth = 4
and @FiscalMonthNum in (10,11,12))
or (@QuarterStartMonth = 5
and @FiscalMonthNum in (11,12,1))
or (@QuarterStartMonth = 6
and @FiscalMonthNum in (12,1,2))
or (@QuarterStartMonth = 7
and @FiscalMonthNum in (1,2,3))
or (@QuarterStartMonth = 8
and @FiscalMonthNum in (2,3,4))
or (@QuarterStartMonth = 9
and @FiscalMonthNum in (3,4,5))
or (@QuarterStartMonth = 10
and @FiscalMonthNum in (4,5,6))
or (@QuarterStartMonth = 11
and @FiscalMonthNum in (5,6,7))
or (@QuarterStartMonth = 12
and @FiscalMonthNum in (6,7,8))
begin
------------------------------- Q3 ------------------------------------
set @FiscalPrevQuarter = 'Q2'
set @FiscalQuarter = 'Q3'
set @FiscalNextQuarter = 'Q4'
if @QuarterStartMonth < 5
begin
set @FiscalPrevYear = @NextYear
set @FiscalYear = @NextYear
set @FiscalNextYear = @NextYear
end
if @QuarterStartMonth > 4
begin
set @FiscalPrevYear = @ThisYear
set @FiscalYear = @ThisYear
set @FiscalNextYear = @ThisYear
end
set @FiscalQuarterMonth = @QuarterStartMonth
+ 6
end
if (@QuarterStartMonth = 1
and @FiscalMonthNum in (10,11,12))
or (@QuarterStartMonth = 2
and @FiscalMonthNum in (11,12,1))
or (@QuarterStartMonth = 3
and @FiscalMonthNum in (12,1,2))
or (@QuarterStartMonth = 4
and @FiscalMonthNum in (1,2,3))
or (@QuarterStartMonth = 5
and @FiscalMonthNum in (2,3,4))
or (@QuarterStartMonth = 6
and @FiscalMonthNum in (3,4,5))
or (@QuarterStartMonth = 7
and @FiscalMonthNum in (4,5,6))
or (@QuarterStartMonth = 8
and @FiscalMonthNum in (5,6,7))
or (@QuarterStartMonth = 9
and @FiscalMonthNum in (6,7,8))
or (@QuarterStartMonth = 10
and @FiscalMonthNum in (7,8,9))
or (@QuarterStartMonth = 11
and @FiscalMonthNum in (8,9,10))
or (@QuarterStartMonth = 12
and @FiscalMonthNum in (9,10,11))
begin
------------------------------- Q4 ------------------------------------
set @FiscalPrevQuarter = 'Q3'
set @FiscalQuarter = 'Q4'
set @FiscalNextQuarter = 'Q1'
set @FiscalPrevYear = @ThisYear
set @FiscalYear = @ThisYear
set @FiscalNextYear = @NextYear
set @FiscalQuarterMonth = @QuarterStartMonth
+ 9
end
end
set @FiscalQuarterYear = @FiscalQuarter
+ '-'
+ @FiscalYear
set @FiscalNextQuarterYear = @FiscalNextQuarter
+ '-'
+ @FiscalNextYear
if @QuarterOnly = 1
set @FiscalQuarterYear = @FiscalQuarter
if @QuarterOnly = 2
set @FiscalQuarterYear = @FiscalYear
if @QuarterOnly = 3
set @FiscalQuarterYear = @FiscalNextQuarter
+ '-'
+ @FiscalNextYear
if @QuarterOnly = 4
set @FiscalQuarterYear = @FiscalNextQuarter
if @QuarterOnly = 5
set @FiscalQuarterYear = @FiscalNextYear
if @QuarterOnly = 6
set @FiscalQuarterYear = @FiscalPrevQuarter
+ '-'
+ @FiscalPrevYear
if @QuarterOnly = 7
set @FiscalQuarterYear = @FiscalPrevQuarter
if @QuarterOnly = 8
set @FiscalQuarterYear = @FiscalPrevYear
if @QuarterOnly = 9
begin
if @FiscalQuarterMonth > 12
set @FiscalQuarterMonth = @FiscalQuarterMonth
- 12
set @FiscalQuarterYear = convert(varchar(2),@FiscalQuarterMonth)
+ '/01/'
+ @ThisYear
end
if @QuarterOnly = 10
set @FiscalQuarterYear = right(@FiscalQuarter,1)
return (@FiscalQuarterYear)
end