
Current Month Amount in Group Currency = Current Month Cumulative Balance - Prev Month Cumulative
Prev Month Amount in Group Currency = Prev Month Cumul Balance - Prev to Prev Month Balance
Current Qtr Amount in Group Currency = Current Month Cumulative Balance - Prev Qtr last Month Balance
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Table Function for Calender'
define table function ZT_FISCPER
returns
{
client : mandt;
fiscalyear : abap.numc(4);
period : abap.numc(3);
Currentfiscper : abap.numc(7);
prevfiscper : abap.numc(7);
prevtoprevfiscper : abap.numc(7);
Curryearfirstfiscper : abap.numc(7);
prevyearCurrentFiscper : abap.numc(7);
prevyearfirstperiod : abap.numc(7);
prevfiscperfirstfiscper : abap.numc(7);
prevtoprevfiscperfirstfiscper : abap.numc(7);
prevyear : abap.numc(4);
prevtoprevyear : abap.numc(4);
currQtrPrevYear : abap.numc(4);
CurrentmonthYrText : abap.char(8);
firstquarter : vdm_yearquarter;
secondquarter : vdm_yearquarter;
thirdquarter : vdm_yearquarter;
fourthquarter : vdm_yearquarter;
Currentquarter : vdm_yearquarter;
Prevquarter : vdm_yearquarter;
Prevyearfirstquarter : vdm_yearquarter;
prevyearquarter : vdm_yearquarter;
prevyearlastquarter : vdm_yearquarter;
CurrQtrlastFiscper : abap.numc(7);
CurrQtrsecondFiscper : abap.numc(7);
CurrQtrFirstFiscper : abap.numc(7);
PrevQtrlastFiscper : abap.numc(7);
PrevQtrFirstFiscper : abap.numc(7);
PrevtoPrevQtrlastFiscper : abap.numc(7);
CurrQtrPrevYrlastFiscper : abap.numc(7);
PrevQtrPrevYrlastFiscper : abap.numc(7);
PrevtoPrevLastFiscper : abap.numc(7);
PrevQtryear : abap.numc(4);
PrevtoPrevQtryear : abap.numc(4);
CurrentYearlastFiscper : abap.numc(7);
PrevYearlastFiscper : abap.numc(7);
FirstmonthofCurrentQtr : abap.numc(7);
FirstmonthofPrevyrCurrentQtr : abap.numc(7);
BeginningFIFiscper : abap.numc(7);
PrevYearBeginningFIFiscper : abap.numc(7);
CurrMinus1fiscper : abap.numc(7);
CurrMinus2fiscper : abap.numc(7);
CurrMinus3fiscper : abap.numc(7);
CurrMinus4fiscper : abap.numc(7);
CurrMinus5fiscper : abap.numc(7);
CurrMinus6fiscper : abap.numc(7);
CurrMinus7fiscper : abap.numc(7);
CurrMinus8fiscper : abap.numc(7);
CurrMinus9fiscper : abap.numc(7);
CurrMinus10fiscper : abap.numc(7);
CurrMinus11fiscper : abap.numc(7);
CurrMinus12fiscper : abap.numc(7);
PrevYrCurrMinus1fiscper : abap.numc(7);
PrevYrCurrMinus2fiscper : abap.numc(7);
PrevYrCurrMinus3fiscper : abap.numc(7);
PrevYrCurrMinus4fiscper : abap.numc(7);
PrevYrCurrMinus5fiscper : abap.numc(7);
PrevYrCurrMinus6fiscper : abap.numc(7);
PrevYrCurrMinus7fiscper : abap.numc(7);
PrevYrCurrMinus8fiscper : abap.numc(7);
PrevYrCurrMinus9fiscper : abap.numc(7);
PrevYrCurrMinus10fiscper : abap.numc(7);
PrevYrCurrMinus11fiscper : abap.numc(7);
PrevYrCurrMinus12fiscper : abap.numc(7);
CurrMinus1Year : abap.numc(4);
CurrMinus2Year : abap.numc(4);
CurrMinus3Year : abap.numc(4);
CurrMinus4Year : abap.numc(4);
CurrMinus5Year : abap.numc(4);
CurrMinus6Year : abap.numc(4);
CurrMinus7Year : abap.numc(4);
CurrMinus8Year : abap.numc(4);
CurrMinus9Year : abap.numc(4);
CurrMinus10Year : abap.numc(4);
CurrMinus11Year : abap.numc(4);
CurrMinus12Year : abap.numc(4);
PrevYrCurrMinus1Year : abap.numc(4);
PrevYrCurrMinus2Year : abap.numc(4);
PrevYrCurrMinus3Year : abap.numc(4);
PrevYrCurrMinus4Year : abap.numc(4);
PrevYrCurrMinus5Year : abap.numc(4);
PrevYrCurrMinus6Year : abap.numc(4);
PrevYrCurrMinus7Year : abap.numc(4);
PrevYrCurrMinus8Year : abap.numc(4);
PrevYrCurrMinus9Year : abap.numc(4);
PrevYrCurrMinus10Year : abap.numc(4);
PrevYrCurrMinus11Year : abap.numc(4);
PrevYrCurrMinus12Year : abap.numc(4);
CurrQtrYrinStr : abap.char(7);
PrevQtrYrinStr : abap.char(7);
CurrQtrPrYrinStr : abap.char(7);
LastPrdofQtr : abap.char(1);
}
implemented by method
zcl_amdp_fiscper=>zfiscper
class ZCL_AMDP_FISCPER definition
public
final
create public .
public section.
interfaces IF_AMDP_MARKER_HDB.
class-methods ZFISCPER for table function ZT_FISCPER.
protected section.
private section.
endclass.
class ZCL_AMDP_FISCPER implementation.
method ZFISCPER
by database function for hdb language sqlscript
options read-only
using SCAL_TT_DATE SEPMRAIMONTHNAME .
I_MAIN = SELECT
DISTINCT
SCL.CALENDARYEAR AS CALENDARYEAR,
SCL.CALENDARMONTH AS CALENDARMONTH,
SCL.CALENDARQUARTER AS CALENDARQUARTER,
SCL.YEARQUARTER AS YEARQUARTER
FROM SCAL_TT_DATE AS SCL
WHERE SCL.CALENDARYEAR >= '2000'
AND SCL.CALENDARYEAR <= '2200'
ORDER BY SCL.CALENDARYEAR;
RETURN SELECT
SESSION_CONTEXT( 'CLIENT' ) AS CLIENT,
SCL.CALENDARYEAR AS FISCALYEAR,
CAST( CONCAT('0', SCL.CALENDARMONTH) AS NVARCHAR(3)) AS PERIOD,
/* FISCAL PERIOD */
CAST( CONCAT( CONCAT( SCL.CALENDARYEAR, '0'), SCL.CALENDARMONTH) as NVARCHAR(7) ) AS CURRENTFISCPER,
/* PREV FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 1 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 1 )) AS NVARCHAR(7) )
end AS PREVFISCPER,
/*PREVIOUS TO PREV FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 2 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 2 )) AS NVARCHAR(7) ) end
end AS PREVTOPREVFISCPER,
/* FISCAL PERIOD */
CAST( CONCAT( CONCAT( SCL.CALENDARYEAR, '0'), '01') AS NVARCHAR(7) ) AS CURRYEARFIRSTFISCPER,
/* PREV YEAR CURRENT PERIOD */
CAST( CONCAT( concat( (SCL.CALENDARYEAR - 1), '0'), SCL.CALENDARMONTH) as NVARCHAR(7) ) AS PREVYEARCURRENTFISCPER ,
/* PREV YEAR First PERIOD */
CAST( CONCAT( CONCAT( (SCL.CALENDARYEAR - 1), '0'), '01') as NVARCHAR(7) ) AS PREVYEARFIRSTPERIOD,
/* PREV FISCPER FIRST FISCPER */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '001') AS NVARCHAR(7) )
ELSE CAST( CONCAT( ( SCL.CALENDARYEAR ), '001') AS NVARCHAR(7) )
END AS PREVFISCPERFIRSTFISCPER,
/* PREV TO PREV FISCPER FIRST FISCPER */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '001') AS NVARCHAR(7) )
ELSE CAST( CONCAT( ( SCL.CALENDARYEAR ), '001') AS NVARCHAR(7) )
END AS PREVTOPREVFISCPERFIRSTFISCPER,
/* PREV FISCAL YEAR */
CAST((SCL.CALENDARYEAR - 1) as NVARCHAR(4) ) as PREVYEAR,
/* PREV FISCAL YEAR */
CAST((SCL.CALENDARYEAR - 2) as NVARCHAR(4) ) as PREVTOPREVYEAR,
/* PREV YEAR of THE current QTR */
case WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '003') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '006') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'
then CAST( CONCAT( (SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'
then CAST( CONCAT( ( SCL.CALENDARYEAR -1 ), '012') AS NVARCHAR(7) )
END END END END AS CURRQTRPREVYEAR,
/* CURRENT MONTH YEAR IN TEXT FORMAT */
CONCAT(CONCAT(MONTHTEXT.MONTHSHORTNAME,'.'),SCL.CALENDARYEAR) AS CURRENTMONTHYRTEXT,
/*FIRST QTR */
CAST( CONCAT( SCL.CALENDARYEAR, 1) as NVARCHAR(5) ) AS FIRSTQUARTER,
/*SECOND QTR */
CAST( CONCAT( SCL.CALENDARYEAR, 2) as NVARCHAR(5) ) AS SECONDQUARTER,
/*THIRD QTR */
CAST( CONCAT( SCL.CALENDARYEAR, 3) as NVARCHAR(5) ) AS THIRDQUARTER,
/*FOURTH QTR */
CAST( CONCAT( SCL.CALENDARYEAR, 4) as NVARCHAR(5) ) AS FOURTHQUARTER,
/*CURRENT QTR */
SCL.YEARQUARTER AS CURRENTQUARTER,
/*PREVIOUS QUARTER */
CASE WHEN SCL.CALENDARQUARTER = '1'
THEN CONCAT( (SCL.CALENDARYEAR - 1),'4' )
else CONCAT( SCL.CALENDARYEAR ,( SCL.CALENDARQUARTER - 1 ) )
END AS PREVQUARTER,
/*PREVIOUS YEAR QTR */
CAST( CONCAT( (SCL.CALENDARYEAR - 1), '1') as NVARCHAR(5) ) AS PREVYEARFIRSTQUARTER,
/*PREVIOUS YEAR QUARTER */
CAST( CONCAT( (SCL.CALENDARYEAR - 1), SCL.CALENDARQUARTER) as NVARCHAR(5) ) AS PREVYEARQUARTER,
/*PREVIOUS YEAR LAST QUARTER */
CAST( CONCAT( (SCL.CALENDARYEAR - 1), '4') as NVARCHAR(5) ) AS PREVYEARLASTQUARTER,
/*CURRENT QTR Last FISCPER */
case WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '003') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '006') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'
then CAST( CONCAT( (SCL.CALENDARYEAR ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '012') AS NVARCHAR(7) )
END END END END AS CURRQTRLASTFISCPER,
/*CURRENT QTR SECOND FISCPER */
case WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '002') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '005') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'
then CAST( CONCAT( (SCL.CALENDARYEAR ), '007') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '010') AS NVARCHAR(7) )
END END END END AS CURRQTRSECONDFISCPER,
/*CURRENT QTR FIRST FISCPER */
CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '001') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '004') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'
then CAST( CONCAT( (SCL.CALENDARYEAR ), '007') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '010') AS NVARCHAR(7) )
END END END END AS CURRQTRFIRSTFISCPER,
/*PREV QTR LAST FISCPER */
CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '003') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'
then CAST( CONCAT( (SCL.CALENDARYEAR ), '006') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '009') AS NVARCHAR(7) )
END END END END AS PREVQTRLASTFISCPER,
/*PREV QTR FIRST FISCPER */
CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '001') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'
then CAST( CONCAT( (SCL.CALENDARYEAR ), '004') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '007') AS NVARCHAR(7) )
END END END END AS PREVQTRFIRSTFISCPER,
/*PREV TO PREV QTR LAST FISCPER */
CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'
then CAST( CONCAT( (SCL.CALENDARYEAR ), '003') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '006') AS NVARCHAR(7) )
END END END END AS PREVTOPREVQTRLASTFISCPER,
/*CURRENT QTR PREV YEAR LAST FISCPER */
CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '003') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '006') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'
then CAST( CONCAT( (SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'
then CAST( CONCAT( ( SCL.CALENDARYEAR -1 ), '012') AS NVARCHAR(7) )
END END END END AS CURRQTRPREVYRLASTFISCPER,
/*PREV QTR PREV YEAR LAST FISCPER */
CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '003') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '006') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )
END END END END AS PREVQTRPREVYRLASTFISCPER,
/* PREV TO PREV YEAR LAST FISCPER */
CAST( CONCAT( (SCL.CALENDARYEAR - 2),'012' ) as NVARCHAR(7) ) as PREVTOPREVLASTFISCPER,
/*PREV QTR YEAR */
case WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'
then CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'
then CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'
then CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'
then CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
END END END END AS PREVQTRYEAR,
/*PREV TO PREV QTR YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'
then CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'
then CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'
then CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'
then CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
END END END END AS PREVTOPREVQTRYEAR,
/* PREV 1ST PERIOD */
CAST( CONCAT( ( SCL.CALENDARYEAR ), '012') AS NVARCHAR(7) ) AS CURRENTYEARLASTFISCPER,
CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) ) AS PREVYEARLASTFISCPER,
/*FIRST FISCAL PERIOD OF THE QTR */
CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '001') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '004') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '007') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'
then CAST( CONCAT( ( SCL.CALENDARYEAR ), '010') AS NVARCHAR(7) )
END END END END AS FIRSTMONTHOFCURRENTQTR,
/*FIRST FISCAL PERIOD OF THE QTR */
CASE WHEN SCL.CALENDARMONTH = '01' or SCL.CALENDARMONTH = '02' or SCL.CALENDARMONTH = '03'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '001') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04' or SCL.CALENDARMONTH = '05' or SCL.CALENDARMONTH = '06'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1), '004') AS NVARCHAR(7) )
else case WHEN SCL.CALENDARMONTH = '07' or SCL.CALENDARMONTH = '08' or SCL.CALENDARMONTH = '09'
then CAST( CONCAT( (SCL.CALENDARYEAR - 1 ), '007') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10' or SCL.CALENDARMONTH = '11' or SCL.CALENDARMONTH = '12'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )
END END END END AS FIRSTMONTHOFPREVYRCURRENTQTR,
/*FI OPENING BALANCE FISCPER */
CAST( CONCAT( ( SCL.CALENDARYEAR ), '000') AS NVARCHAR(7) ) AS BEGINNINGFIFISCPER,
/*PREV YEAR FI OPENING BALANCE FISCPER */
CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '000') AS NVARCHAR(7) ) AS PREVYEARBEGINNINGFIFISCPER,
/* PREV FISCAL PERIOD */
CASE WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 1 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 1 )) AS NVARCHAR(7) )
end AS CURRMINUS1FISCPER,
/* PREV SECOND FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 2 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 2 )) AS NVARCHAR(7) ) end
end AS CURRMINUS2FISCPER,
/* PREV THIRD FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 3 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 3 )) AS NVARCHAR(7) ) end end
end AS CURRMINUS3FISCPER,
/* PREV 4TH FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 4 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 4 )) AS NVARCHAR(7) ) end end end
end AS CURRMINUS4FISCPER,
/* PREV 5TH FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '008') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '05'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 5 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 5 )) AS NVARCHAR(7) ) end end end end
end AS CURRMINUS5FISCPER,
/* PREV 6TH FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '007') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '008') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '05'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '06'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 6 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 6 )) AS NVARCHAR(7) ) end end end end end
end AS CURRMINUS6FISCPER,
/* PREV 7TH FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '006') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '007') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '008') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '05'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '06'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 7 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 7 )) AS NVARCHAR(7) ) end end end end end end
end AS CURRMINUS7FISCPER,
/* PREV 8TH FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '005') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '006') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '007') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '008') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '05'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '06'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '08'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 8 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 8 )) AS NVARCHAR(7) ) end end end end end end end
end AS CURRMINUS8FISCPER,
/* PREV 9TH FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '004') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '005') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '006') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '007') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '05'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '008') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '06'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '08'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '09'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 9 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 9 )) AS NVARCHAR(7) ) end end end end end end end end
end AS CURRMINUS9FISCPER,
/* PREV 10TH FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '003') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '004') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '005') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '006') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '05'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '007') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '06'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '008') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '08'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '09'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 10 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 10 )) AS NVARCHAR(7) ) end end end end end end end end end
end AS CURRMINUS10FISCPER,
/* PREV 11TH FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '002') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '003') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '004') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '005') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '05'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '006') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '06'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '007') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '008') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '08'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '09'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '11'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR, CASE WHEN ( SCL.CALENDARMONTH - 11 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 11 )) AS NVARCHAR(7) ) end end end end end end end end end end
end AS CURRMINUS11FISCPER,
/* PREV 12TH FISCAL PERIOD */
CAST( CONCAT( CONCAT( SCL.CALENDARYEAR - 1, '0'), SCL.CALENDARMONTH) as NVARCHAR(7) ) AS CURRMINUS12FISCPER,
/* PREV YEAR PREV FISCAL PERIOD */
CASE WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 1 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 1 )) AS NVARCHAR(7) )
end AS PREVYRCURRMINUS1FISCPER,
/* PREV YEAR PREV SECOND FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 2 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 2 )) AS NVARCHAR(7) ) end
end AS PREVYRCURRMINUS2FISCPER,
/* PREV YEAR PREV THIRD FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 3 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 3 )) AS NVARCHAR(7) ) end end
end AS PREVYRCURRMINUS3FISCPER,
/* PREV YEAR PREV 4TH FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 4 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 4 )) AS NVARCHAR(7) ) end end end
end AS PREVYRCURRMINUS4FISCPER,
/* PREV YEAR PREV 5TH FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '008') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '05'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 5 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 5 )) AS NVARCHAR(7) ) end end end end
end AS PREVYRCURRMINUS5FISCPER,
/* PREV YEAR PREV 6TH FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '007') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '008') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '05'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '06'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 6 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 6 )) AS NVARCHAR(7) ) end end end end end
end AS PREVYRCURRMINUS6FISCPER,
/* PREV YEAR PREV 7TH FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '006') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '007') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '008') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '05'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '06'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 7 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 7 )) AS NVARCHAR(7) ) end end end end end end
end AS PREVYRCURRMINUS7FISCPER,
/* PREV YEAR PREV 8TH FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '005') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '006') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '007') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '008') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '05'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '06'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '08'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 8 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 8 )) AS NVARCHAR(7) ) end end end end end end end
end AS PREVYRCURRMINUS8FISCPER,
/* PREV YEAR PREV 9TH FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '004') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '005') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '006') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '007') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '05'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '008') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '06'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '08'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '09'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 9 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 9 )) AS NVARCHAR(7) ) end end end end end end end end
end AS PREVYRCURRMINUS9FISCPER,
/* PREV YEAR PREV 10TH FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '003') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '004') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '005') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '006') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '05'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '007') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '06'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '008') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '08'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '09'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 10 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 10 )) AS NVARCHAR(7) ) end end end end end end end end end
end AS PREVYRCURRMINUS10FISCPER,
/* PREV YEAR PREV 11TH FISCAL PERIOD */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '002') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '02'
then CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '003') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '03'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '004') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '04'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '005') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '05'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '006') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '06'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '007') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '07'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '008') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '08'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '009') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '09'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '010') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '10'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '011') AS NVARCHAR(7) )
ELSE CASE WHEN SCL.CALENDARMONTH = '11'
THEN CAST( CONCAT( ( SCL.CALENDARYEAR - 2 ), '012') AS NVARCHAR(7) )
ELSE CAST( CONCAT( concat( SCL.CALENDARYEAR - 1, CASE WHEN ( SCL.CALENDARMONTH - 11 ) < 10 THEN '00' ELSE '0' END ), ( SCL.CALENDARMONTH - 11 )) AS NVARCHAR(7) ) end end end end end end end end end end
end AS PREVYRCURRMINUS11FISCPER,
/* PREV YEAR PREV 12TH FISCAL PERIOD 014467*/
CAST( CONCAT( CONCAT( SCL.CALENDARYEAR - 2, '0'), SCL.CALENDARMONTH) as NVARCHAR(7) ) AS PREVYRCURRMINUS12FISCPER,
/*CURR PERIOD -1 YEAR */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
END AS CURRMINUS1YEAR,
/*CURR PERIOD -2 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02'
then CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
END AS CURRMINUS2YEAR,
/*CURR PERIOD -3 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03'
then CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
END AS CURRMINUS3YEAR,
/*CURR PERIOD -4 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
then CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
END AS CURRMINUS4YEAR,
/*CURR PERIOD -5 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
OR SCL.CALENDARMONTH = '05'
then CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
END AS CURRMINUS5YEAR,
/*CURR PERIOD -6 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
OR SCL.CALENDARMONTH = '05' OR SCL.CALENDARMONTH = '06'
then CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
END AS CURRMINUS6YEAR,
/*CURR PERIOD -7 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
OR SCL.CALENDARMONTH = '05' OR SCL.CALENDARMONTH = '06' OR SCL.CALENDARMONTH = '07'
then CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
END AS CURRMINUS7YEAR,
/*CURR PERIOD -8 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
OR SCL.CALENDARMONTH = '05' OR SCL.CALENDARMONTH = '06' OR SCL.CALENDARMONTH = '07' OR SCL.CALENDARMONTH = '08'
then CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
END AS CURRMINUS8YEAR,
/*CURR PERIOD -9 YEAR 014467*/
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
OR SCL.CALENDARMONTH = '05' OR SCL.CALENDARMONTH = '06' OR SCL.CALENDARMONTH = '07' OR SCL.CALENDARMONTH = '08'
OR SCL.CALENDARMONTH = '09'
then CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
END AS CURRMINUS9YEAR,
/*CURR PERIOD -10 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
OR SCL.CALENDARMONTH = '05' OR SCL.CALENDARMONTH = '06' OR SCL.CALENDARMONTH = '07' OR SCL.CALENDARMONTH = '08'
OR SCL.CALENDARMONTH = '09' OR SCL.CALENDARMONTH = '10'
then CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
END AS CURRMINUS10YEAR,
/*CURR PERIOD -11 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
OR SCL.CALENDARMONTH = '05' OR SCL.CALENDARMONTH = '06' OR SCL.CALENDARMONTH = '07' OR SCL.CALENDARMONTH = '08'
OR SCL.CALENDARMONTH = '09' OR SCL.CALENDARMONTH = '10' OR SCL.CALENDARMONTH = '11'
then CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
END AS CURRMINUS11YEAR,
/*CURR PERIOD -12 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
OR SCL.CALENDARMONTH = '05' OR SCL.CALENDARMONTH = '06' OR SCL.CALENDARMONTH = '07' OR SCL.CALENDARMONTH = '08'
OR SCL.CALENDARMONTH = '09' OR SCL.CALENDARMONTH = '10' OR SCL.CALENDARMONTH = '11' OR SCL.CALENDARMONTH = '12'
then CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR ) AS NVARCHAR(4) )
END AS CURRMINUS12YEAR,
/*PREV YR CURR PERIOD -1 YEAR */
case WHEN SCL.CALENDARMONTH = '01'
then CAST( ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
END AS PREVYRCURRMINUS1YEAR,
/*PREV YR CURR PERIOD -2 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02'
then CAST( ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
END AS PREVYRCURRMINUS2YEAR,
/*PREV YR CURR PERIOD -3 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03'
then CAST( ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
END AS PREVYRCURRMINUS3YEAR,
/*PREV YR CURR PERIOD -4 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
then CAST( ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
END AS PREVYRCURRMINUS4YEAR,
/*PREV YR CURR PERIOD -5 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
OR SCL.CALENDARMONTH = '05'
then CAST( ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
END AS PREVYRCURRMINUS5YEAR,
/*PREV YR CURR PERIOD -6 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
OR SCL.CALENDARMONTH = '05' OR SCL.CALENDARMONTH = '06'
then CAST( ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
END AS PREVYRCURRMINUS6YEAR,
/*PREV YR CURR PERIOD -7 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
OR SCL.CALENDARMONTH = '05' OR SCL.CALENDARMONTH = '06' OR SCL.CALENDARMONTH = '07'
then CAST( ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
END AS PREVYRCURRMINUS7YEAR,
/*PREV YR CURR PERIOD -8 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
OR SCL.CALENDARMONTH = '05' OR SCL.CALENDARMONTH = '06' OR SCL.CALENDARMONTH = '07' OR SCL.CALENDARMONTH = '08'
then CAST( ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
END AS PREVYRCURRMINUS8YEAR,
/*PREV YR CURR PERIOD -9 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
OR SCL.CALENDARMONTH = '05' OR SCL.CALENDARMONTH = '06' OR SCL.CALENDARMONTH = '07' OR SCL.CALENDARMONTH = '08'
OR SCL.CALENDARMONTH = '09'
then CAST( ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
END AS PREVYRCURRMINUS9YEAR,
/*PREV YR CURR PERIOD -10 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
OR SCL.CALENDARMONTH = '05' OR SCL.CALENDARMONTH = '06' OR SCL.CALENDARMONTH = '07' OR SCL.CALENDARMONTH = '08'
OR SCL.CALENDARMONTH = '09' OR SCL.CALENDARMONTH = '10'
then CAST( ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
END AS PREVYRCURRMINUS10YEAR,
/*PREV YR CURR PERIOD -11 YEAR 014467*/
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
OR SCL.CALENDARMONTH = '05' OR SCL.CALENDARMONTH = '06' OR SCL.CALENDARMONTH = '07' OR SCL.CALENDARMONTH = '08'
OR SCL.CALENDARMONTH = '09' OR SCL.CALENDARMONTH = '10' OR SCL.CALENDARMONTH = '11'
then CAST( ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
END AS PREVYRCURRMINUS11YEAR,
/*PREV YR CURR PERIOD -12 YEAR */
CASE WHEN SCL.CALENDARMONTH = '01' OR SCL.CALENDARMONTH = '02' OR SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '04'
OR SCL.CALENDARMONTH = '05' OR SCL.CALENDARMONTH = '06' OR SCL.CALENDARMONTH = '07' OR SCL.CALENDARMONTH = '08'
OR SCL.CALENDARMONTH = '09' OR SCL.CALENDARMONTH = '10' OR SCL.CALENDARMONTH = '11' OR SCL.CALENDARMONTH = '12'
then CAST( ( SCL.CALENDARYEAR - 2 ) AS NVARCHAR(4) )
ELSE CAST( ( SCL.CALENDARYEAR - 1 ) AS NVARCHAR(4) )
END AS PREVYRCURRMINUS12YEAR,
/*CURRENT QTR IN STRING FORMAT */
CAST ( CONCAT( SCL.CALENDARYEAR ,CONCAT( '.Q', ( SCL.CALENDARQUARTER ) ) ) AS VARCHAR(7) ) AS CURRQTRYRINSTR,
/*PREV QTR IN STRING FORMAT */
CASE WHEN SCL.CALENDARQUARTER = '1'
THEN CAST( CONCAT( (SCL.CALENDARYEAR - 1), CONCAT ( '.Q','4' ) ) AS VARCHAR(7) )
else CAST( CONCAT( SCL.CALENDARYEAR , CONCAT ( '.Q' ,( SCL.CALENDARQUARTER - 1 ) ) ) AS VARCHAR(7) )
END AS PREVQTRYRINSTR,
/*CURRENT QTR IN STRING FORMAT */
CAST( CONCAT( ( SCL.CALENDARYEAR - 1 ),CONCAT( '.Q', ( SCL.CALENDARQUARTER ) ) ) AS VARCHAR(7) ) AS CURRQTRPRYRINSTR,
/*FLAG LAST PERIOD OF THE QTR*/
CASE WHEN SCL.CALENDARMONTH = '03' OR SCL.CALENDARMONTH = '06' OR SCL.CALENDARMONTH = '09' OR SCL.CALENDARMONTH = '12'
THEN 'X' ELSE '' END AS LASTPRDOFQTR
FROM :I_MAIN AS SCL
LEFT OUTER JOIN SEPMRAIMONTHNAME AS MONTHTEXT
ON MONTHTEXT.LANGUAGE = 'E'
and SCL.CALENDARMONTH = MONTHTEXT.CALENDARMONTH;
ENDMETHOD.
endclass.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
3 | |
3 | |
3 | |
2 | |
2 | |
2 | |
1 | |
1 | |
1 | |
1 |