It
not very easy to get different date description in report development,
especially with DMR model. The solution is to create customized date dimension
properties in framework manager. It will provide following advantages:
1)
Unified logic in framework manager, it
will be much easy to change if needed.2) Speed up report development, as report can simply just use the domain properties, or role value
select
DimDate.FISCAL_YEAR_STR as Fiscal_Year__YYYY_,('FY' || substring(DimDate.FISCAL_YEAR_STR from 3 for 2)) as Fiscal_Year__FYNN_,
DimDate.FISCAL_QUARTER_STR as Fiscal_Quarter__QN_,
DimDate.FISCAL_MONTH_STR_13 as Fiscal_Month__13_,
((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) as Fiscal_Period_ABS_Value__N_,
((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end) as Fiscal_Quarter_ABS_Value__N_,
((DimDate.FISCAL_YEAR_STR || ' - ') || DimDate.FISCAL_QUARTER_STR) as Fiscal_YQ__YYYYQN_,
(DimDate.FISCAL_QUARTER_STR || substr(DimDate.FISCAL_YEAR_STR,3,2)) as Fis_YQ__QNYY_,
((DimDate.FISCAL_QUARTER_STR || '-') || substr(DimDate.FISCAL_YEAR_STR,3,2)) as c9,
(2016 - DimDate.FISCAL_YEAR_STR) as Rolling_Fiscal_Year__iN_,
cast((2016 - DimDate.FISCAL_YEAR_STR) as varchar (20)) as Rolling_Fiscal_Year_String__N_,
(8068 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end)) as Rolling_Fiscal_Quarter__iN_,
cast((8068 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end)) as varchar (20)) as Rolling_Fiscal_Quarter_Str__N_,
(24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) as Rolling_Fiscal_Period__iN_,
cast((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) as varchar (20)) as Rolling_Period_String__N_,
case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 1),12)) then 'FYTD'
else 'NON-FYTD'
end as FYTD_Ctg__FYTD_NON_FYTD_,
case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),3) <= mod((24202 - 1),3)) then 'FQTD'
else 'NON-FQTD'
end as FQTD_Ctg__FQTD_NON_FQTD_,
((cast((2016 - DimDate.FISCAL_YEAR_STR) as varchar (20)) || '-') || case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 1),12)) then 'FYTD'
else 'NON-FYTD'
end) as R_FYTD_Ctg__N_FYTD_NON_FYTD_,
(case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 1),12)) then 'FYTD'
else 'NON-FYTD'
end || substring(DimDate.FISCAL_YEAR_STR from 3 for 2)) as R_FYTD_Ctg_FYTDYY_NON_FYTDYY_,
((cast((8068 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end)) as varchar (20)) || '-') || case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),3) <= mod((24202 - 1),3)) then 'FQTD'
else 'NON-FQTD'
end) as R_FQTD_Ctg__N_FQTD_NON_FQTD_,
(2016 - DimDate.FISCAL_YEAR_STR) as Rolling_FY__iN__By_Lag1P,
cast((2016 - DimDate.FISCAL_YEAR_STR) as varchar (20)) as Rolling_FY_Str__N__By_Lag1P,
(8067 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end)) as Rolling_FQ__iN__By_Lag1P,
cast((8067 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end)) as varchar (20)) as Rolling_FQ_Str__N__By_Lag1P,
(24201 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) as Rolling_FP__iN__By_Lag1P,
cast((24201 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) as varchar (20)) as Rolling_P_Str__N__By_Lag1P,
case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 2),12)) then 'FYTD'
else 'NON-FYTD'
end as FYTD_Ctg_FYTD__Lag1P,
case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),3) <= mod((-2),3)) then 'FQTD'
else 'NON-FQTD'
end as FQTD_Ctg_FQTD__Lag1P,
((cast((2016 - DimDate.FISCAL_YEAR_STR) as varchar (20)) || '-') || case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 2),12)) then 'FYTD'
else 'NON-FYTD'
end) as R_FYTD_Ctg__NFYTD__By_Lag1P,
(case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 2),12)) then 'FYTD'
else 'NON-FYTD'
end || substring(DimDate.FISCAL_YEAR_STR from 3 for 2)) as R_FYTD_Ctg__FYTDYY_Lag1P,
case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),3) <= mod((-2),3)) then 'FQTD'
else 'NON-FQTD'
end as R_FQTD_Ctg_FQTDYY_Lag1P,
(2016 - DimDate.FISCAL_YEAR_STR) as R_FY__iN__By_Lag2P,
cast((2016 - DimDate.FISCAL_YEAR_STR) as varchar (20)) as R_FY_Str_N__By_Lag2P,
(8067 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end)) as R_FQ__iN__By_Lag2P,
cast((8067 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end)) as varchar (20)) as R_FQ_Str__N__By_Lag2P,
(24200 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) as R_FP__iN__By_Lag2P,
cast((24200 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) as varchar (20)) as R_P_Str__N__By_Lag2P,
case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 3),12)) then 'FYTD'
else 'NON-FYTD'
end as FYTD_Ctg__FYTD__By_Lag2P,
case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),3) <= mod((-3),3)) then 'FQTD'
else 'NON-FQTD'
end as FQTD_Ctg__FQTD__By_Lag2P,
((cast((2016 - DimDate.FISCAL_YEAR_STR) as varchar (20)) || '-') || case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 3),12)) then 'FYTD'
else 'NON-FYTD'
end) as R_FYTD_Ctg__NFYTD__By_Lag2P,
(case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 3),12)) then 'FYTD'
else 'NON-FYTD'
end || substring(DimDate.FISCAL_YEAR_STR from 3 for 2)) as R_FYTD_Ctg__FYTDYY_Lag2P,
case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),3) <= mod((-3),3)) then 'FQTD'
else 'NON-FQTD'
end as R_FQTD_Ctg__FQTDYY_Lag2P,
case
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 11)) then 'Rolling First 12 Periods'
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 12) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 23)) then 'Rolling Second 12 Periods'
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 24) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 25)) then 'Rolling Series Remainiing 2 Periods'
else 'Non-Rolling 26 Periods'
end as Rolling_26_P_Sub_Ctg,
case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end as Calendar_Year__YYYY_,
(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end || case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
else DimDate.FISCAL_MONTH_STR_13
end) as Calendar_YM__YYYYMM_,
((case case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
else DimDate.FISCAL_MONTH_STR_13
end
when '02' then 'Feb'
when '03' then 'Mar'
when '04' then 'Apr'
when '05' then 'May'
when '06' then 'Jun'
when '07' then 'Jul'
when '08' then 'Aug'
when '09' then 'Sep'
when '10' then 'Oct'
when '11' then 'Nov'
when '12' then 'Dec'
when '01' then 'Jan'
else 'Ukn'
end || '-') || case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end) as Ca_YM__Mmm_YYYY_,
((case case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
else DimDate.FISCAL_MONTH_STR_13
end
when '02' then 'February'
when '03' then 'March'
when '04' then 'April'
when '05' then 'May'
when '06' then 'June'
when '07' then 'July'
when '08' then 'August'
when '09' then 'September'
when '10' then 'October'
when '11' then 'November'
when '12' then 'December'
when '01' then 'January'
else 'Ukn'
end || ' ') || case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end) as Ca_YM__Month_YYYY_,
case case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
else DimDate.FISCAL_MONTH_STR_13
end
when '02' then 'F'
when '03' then 'M'
when '04' then 'A'
when '05' then 'M'
when '06' then 'J'
when '07' then 'J'
when '08' then 'A'
when '09' then 'S'
when '10' then (('Oct' || '-') || substring(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end from 3 for 2))
when '11' then 'N'
when '12' then 'D'
when '01' then 'J'
else 'Unk'
end as Ca_Month__M_Oct_YY_,
case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
else DimDate.FISCAL_MONTH_STR_13
end as Ca_Month__MM_,
case case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
else DimDate.FISCAL_MONTH_STR_13
end
when '11' then 1
when '12' then 2
when '01' then 3
when '02' then 4
when '03' then 5
when '04' then 6
when '05' then 7
when '06' then 8
when '07' then 9
when '08' then 10
when '09' then 11
when '10' then 12
else 13
end as Ca_M_Sort_In_FM,
case case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
else DimDate.FISCAL_MONTH_STR_13
end
when '02' then 'F'
when '03' then 'M'
when '04' then 'A'
when '05' then 'M'
when '06' then 'J'
when '07' then 'J'
when '08' then 'A'
when '09' then 'S'
when '10' then 'O'
when '11' then 'N'
when '12' then 'D'
when '01' then 'J'
else 'U'
end as Ca_Month__M_,
case case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
else DimDate.FISCAL_MONTH_STR_13
end
when '02' then 'Feb'
when '03' then 'Mar'
when '04' then 'Apr'
when '05' then 'May'
when '06' then 'Jun'
when '07' then 'Jul'
when '08' then 'Aug'
when '09' then 'Sep'
when '10' then 'Oct'
when '11' then 'Nov'
when '12' then 'Dec'
when '01' then 'Jan'
else 'Unk'
end as Ca_Month__Mmm_,
case
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 11)) then 'Rolling 12 Periods'
when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 12 Periods'
else 'Past Non-Rolling 12 Periods'
end as Rolling_12_Period_Category,
case
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 12)) then 'Rolling 13 Periods'
when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 13 Periods'
else 'Past Non-Rolling 13 Periods'
end as Rolling_13_Period_Category,
case
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 13)) then 'Rolling 14 Periods'
when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 14 Periods'
else 'Past Non-Rolling 14 Periods'
end as Rolling_14_Period_Category,
case
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 14)) then 'Rolling 15 Periods'
when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 15 Periods'
else 'Past Non-Rolling 15 Periods'
end as Rolling_15_Period_Category,
case
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 15)) then 'Rolling 16 Periods'
when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 16 Periods'
else 'Past Non-Rolling 16 Periods'
end as Rolling_16_Period_Category,
case
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 17)) then 'Rolling 18 Periods'
when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 18 Periods'
else 'Past Non-Rolling 18 Periods'
end as Rolling_18_Period_Category,
case
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 22)) then 'Rolling 23 Periods'
when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 23 Periods'
else 'Past Non-Rolling 23 Periods'
end as Rolling_23_Periods_Category,
case
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 25)) then 'Rolling 26 Periods'
when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 26 Periods'
else 'Past Non-Rolling 26 Periods'
end as Rolling_26_Period_Category,
case
when (((24200 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24200 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 11)) then 'Rolling 12 Periods'
when ((24200 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 12 Periods'
else 'Past Non-Rolling 12 Periods'
end as R_12_P_Ctg_By_Lag2P,
mod(((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) + 12000),12) as RP_in_12_P_Cycle__iN_,
((DimDate.FISCAL_YEAR_STR || ' - ') || DimDate.FISCAL_MONTH_STR_13) as Fiscal_Year_And_Month__YYYY13_,
floor(((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) / 12)) as Repet_R_Cycl_in_12_PCycl__iN_,
cast(mod(((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) + 12000),12) as varchar (20)) as Repet_R_Period_in_12_PCycle,
cast(floor(((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) / 12)) as varchar (20)) as Repet_R_Cycle_in_12_PCycle_Str,
((cast(mod(((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) + 12000),12) as varchar (20)) || ' - ') || cast(floor(((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) / 12)) as varchar (20))) as Repet_RP_in_12_P_Cycle_Full,
((cast(floor(((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) / 12)) as varchar (20)) || ' - ') || cast(mod(((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) + 12000),12) as varchar (20))) as Repet_R_Cycl_in_12P_Cycl_Full,
(2016 - case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end) as Rolling_Calendar_Year__iN_,
cast((2016 - case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end) as varchar (20)) as Rolling_CY_String__N_,
cast((8067 - ((cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end as int) * 4) + case DimDate.CALENDAR_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end)) as varchar (20)) as c71,
(8067 - ((cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end as int) * 4) + case DimDate.CALENDAR_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end)) as Rolling_Calendar_Quarter__iN_,
((cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end as int) * 12) + cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
else DimDate.FISCAL_MONTH_STR_13
end as int)) as Ca_Period_ABS_Value__N_,
DimDate.CALENDAR_QUARTER_STR as Calendar_Quarter__QN_,
((cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end as int) * 4) + case DimDate.CALENDAR_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end) as Ca_Quarter_ABS_Value__N_,
(8067 - ((cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end as int) * 4) + case DimDate.CALENDAR_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end)) as Rolling_CQ__iN_,
cast((8067 - ((cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end as int) * 4) + case DimDate.CALENDAR_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end)) as varchar (20)) as Rolling_CQ_Str__N_,
case when (mod((((cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end as int) * 12) + cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
else DimDate.FISCAL_MONTH_STR_13
end as int)) - 1),12) <= mod((24200 - 1),12)) then 'CYTD'
else 'NON-CYTD'
end as CYTD_Ctg__CYTD_,
case when (mod((((cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end as int) * 12) + cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
else DimDate.FISCAL_MONTH_STR_13
end as int)) - 1),3) <= mod((24200 - 1),3)) then 'FQTD'
else 'NON-FQTD'
end as CQTD_Ctg__CQTD_,
((cast((2016 - case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end) as varchar (20)) || '-') || case when (mod((((cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end as int) * 12) + cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
else DimDate.FISCAL_MONTH_STR_13
end as int)) - 1),12) <= mod((24200 - 1),12)) then 'CYTD'
else 'NON-CYTD'
end) as R_CYTD_Ctg__N_CYTD_,
(case when (mod((((cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end as int) * 12) + cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
else DimDate.FISCAL_MONTH_STR_13
end as int)) - 1),12) <= mod((24200 - 1),12)) then 'CYTD'
else 'NON-CYTD'
end || substring(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end from 3 for 2)) as R_CYTD_Ctg__CYTDYY_,
((cast((8067 - ((cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end as int) * 4) + case DimDate.CALENDAR_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end)) as varchar (20)) || '-') || case when (mod((((cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end as int) * 12) + cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
else DimDate.FISCAL_MONTH_STR_13
end as int)) - 1),3) <= mod((24200 - 1),3)) then 'FQTD'
else 'NON-FQTD'
end) as R_CQTD_Ctg__N_CQTD_,
('CY' || substring(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end from 3 for 2)) as Ca_Year__CYNN_,
((case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end || ' - ') || DimDate.CALENDAR_QUARTER_STR) as Ca_YQ__YYYYQN_,
DimDate.CALENDAR_FIRST_DAY_OF_MONTH as Ca_1st_Day_Of_Month,
DimDate.CALENDAR_LAST_DAY_OF_MONTH as Ca_Lst_Day_Of_Month,
DimDate.CALENDAR_FIRST_DAY_OF_YEAR as Ca_1st_Day_Of_Year,
DimDate.CALENDAR_LAST_DAY_OF_YEAR as Ca_Lst_Day_Of_Year,
to_char(DimDate.CALENDAR_FIRST_DAY_OF_MONTH,'YYYY-MM-DD') as Ca_1st_DOfM__YYYY_MM_01_,
to_char(DimDate.CALENDAR_LAST_DAY_OF_MONTH,'YYYY-MM-DD') as Ca_Lst_DOfM_YYYY_MM_31_,
to_char(DimDate.CALENDAR_FIRST_DAY_OF_MONTH,'YYYY-Mon-DD') as Ca_1st_DOfM__YYYY_Mmm_01_,
to_char(DimDate.CALENDAR_LAST_DAY_OF_MONTH,'YYYY-Mon-DD') as Ca_Lst_DOfM__YYYY_Mmm_31_,
((((substr(to_char(DimDate.CALENDAR_LAST_DAY_OF_MONTH,'YYYY-Mon-DD'),10,2) || '-') || substr(to_char(DimDate.CALENDAR_LAST_DAY_OF_MONTH,'YYYY-Mon-DD'),6,3)) || '-') || substr(to_char(DimDate.CALENDAR_LAST_DAY_OF_MONTH,'YYYY-Mon-DD'),3,2)) as Ca_Lst_DOfM__31_Mmm_YY_,
DimDate.FISCAL_QUERTER_END_MONTH as Fis_Q_End_Mon__YYYY01_,
((case substring(DimDate.FISCAL_QUERTER_END_MONTH from 5 for 2)
when '01' then 'Jan'
when '02' then 'Feb'
when '03' then 'Mar'
when '04' then 'Apr'
when '05' then 'May'
when '06' then 'Jun'
when '07' then 'Jul'
when '08' then 'Aug'
when '09' then 'Sep'
when '10' then 'Oct'
when '11' then 'Nov'
when '12' then 'Dec'
else 'Unk'
end || '-') || substring(DimDate.FISCAL_QUERTER_END_MONTH from 3 for 2)) as Fis_Q_End_Mon__Jan_YY_,
((case case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
else DimDate.FISCAL_MONTH_STR_13
end
when '02' then 'Feb'
when '03' then 'Mar'
when '04' then 'Apr'
when '05' then 'May'
when '06' then 'Jun'
when '07' then 'Jul'
when '08' then 'Aug'
when '09' then 'Sep'
when '10' then 'Oct'
when '11' then 'Nov'
when '12' then 'Dec'
when '01' then 'Jan'
else 'Ukn'
end || '-') || substring(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end from 3 for 2)) as Ca_YM__Mmm_YY_,
((case substring(cast((100 + (floor(((cast(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
else DimDate.FISCAL_MONTH_STR_13
end as int) + 2) / 3)) * 3)) as varchar (20)) from 2 for 2)
when '01' then 'Jan'
when '02' then 'Feb'
when '03' then 'Mar'
when '04' then 'Apr'
when '05' then 'May'
when '06' then 'Jun'
when '07' then 'Jul'
when '08' then 'Aug'
when '09' then 'Sep'
when '10' then 'Oct'
when '11' then 'Nov'
when '12' then 'Dec'
end || '-') || substring(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end from 3 for 2)) as Ca_Q_End_Mon_Mar_YY_,
(DimDate.CALENDAR_QUARTER_STR || substring(case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end from 3 for 2)) as Calendar_YQ__QNYY_,
((case
when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
else DimDate.FISCAL_YEAR_STR
end || ' - ') || DimDate.CALENDAR_QUARTER_STR) as Calendar_YQ__YYYYQN_,
case
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 11)) then 'Rolling First 12 Periods'
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 12) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 29)) then 'Rolling Series Remainiin 18 Periods'
else 'Non-Rolling 30 Periods'
end as Rolling_30_P_Sub_Ctg,
case
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 29)) then 'Rolling 30 Periods'
when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 30 Periods'
else 'Past Non-Rolling 30 Periods'
end as Rolling_30_Period_Category,
case
when (((8068 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end)) >= 0) and ((8068 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end)) <= 31)) then 'Rolling 32 Fiscal Qarters'
when ((8068 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
when 'Q1' then 1
when 'Q2' then 2
when 'Q3' then 3
when 'Q4' then 4
end)) < 0) then 'The Future Periods Non-Rolling 32 Fiscal Qarters'
else 'Past Non-Rolling 32 Fiscal Qarters'
end as c102,
cast(to_char(DimDate.CALENDAR_LAST_DAY_OF_MONTH,'Month DD, YYYY') as date) as Ca_Lst_DOfM__Mmm_31__YYYY_,
case
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 11)) then 'Rolling First 12 Periods'
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 12) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 59)) then 'Rolling Series Remainiin 48 Periods'
else 'Non-Rolling 60 Periods'
end as Rolling_60_P_Sub_Ctg,
case
when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 59)) then 'Rolling 60 Periods'
when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 60 Periods'
else 'Past Non-Rolling 60 Periods'
end as Rolling_60_Period_Category
from DimDate
No comments:
Post a Comment