Technical Articles
Table Function for Calendar/Date Attributes
This Blog would give us most of the information about calendar attributes or date attributes through ABAP CDS. I am involved in Group Reporting implementation where we have to deal with lot of date attributes.
Little background on Group reporting, the data from group reporting is stored in ACDOCU (Consolidation Data table) where the data can be stored as Consolidated or Pre-consolidated which differentiated by RECORD TYPE. Consolidated data will have RECORD TYPE ‘R’, and the data is stored in cumulative manner ( Balance Carry Forward). For Balance Sheet reports if we want report DEC.2020 value then if you can directly the value of last period of the year 2020 and also YTD value for the 2020 can derived by reading last period value of 2020 as Record Type R will have Balance Carry forward. But the complexity increases when you are creating or designing the P&L reports where we need to show periodic values of the each month instead of Balance Carry forward.
Below are few KPI’s where you need Date attributes.
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 BalanceSo Order to achieve all these KPI’s in CDS views we need to have Date function/attributes defined so that our KPI definitions would be easy.
As first step we need to create a table function. AMDP refer this for any questions on AMDP.
@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
Below is the code for the AMDP method called 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.
upon activation you can execute the table function like a CDS which would give us below result.
We can filter to get the distinct records
you can consume the Table function like any other CDS view using Association or Join to make use of the attributes, Here is how I consumed the Table function
Hope this blog helps, Please comment so that I can try to answer, Happy learning.
Thanks,
Shanthi
Hi Shanti Bhasker,
It is really help full my requirement ,I would like to know how to join date functions view with actual CDS view to get the data .Please suggest I need to apply the parameters (Year and Period) to Actuals CDS view ..I applied parameter for date functions but it is not filter out actual CDS view data .
Please share the code to my symega2021@gmail.com
Thanks in advance .
Kind Regards,
Guru
Sorry for delayed response
ZT_FISCPER is the my table function
Hello,
are you aware of the SAP Standard CDS View I_CalendarDate?
Best Regards
Gregor