The script works if i manually type (@CurrentPeriod-1, -1, 0, 0, 0)
or (@CurrentPeriod, -1, 0, 0, 0)
but not in loop
set a=0;
Do while a>-13
ODS:
Load Period,
if(len(Period)=0,'Null','Not Null') as Period_null,
IsNull(Period) as Period_nulll,
CorpCode,
RecNo() as dist,
CostCentre as CC_TESTODS,
'CC_'&CostCentre as CostCentre_ods,
'CC_'&Text(CostCentre )as CostCentre,
if(len(CostCentre)=0,'Null','Not Null') as CostCentreNull_ODS,
Trim(CombinedAccountCode) as CombinedAccountCode,
if(len(CombinedAccountCode)=0,'Null','Not Null') as Acccode_ODS,
ActLcYtd,
ActZarYtd
;
SQL DECLARE @CurrentPeriod INT
SELECT @CurrentPeriod = RP_Period
FROM FCODS.md.WorkDayCalendar
WHERE [Date] = CAST( GETDATE() AS Date )
SELECT DATEFROMPARTS(Period/ 100, Period - (Period/100)*100, 1) AS Period,
CorpCode,
CostCentre,
CombinedAccountCode,
ActLcYtd,
ActZarYtd
FROM
(
SELECT Period,
CorpCode,
CostCentre,
CombinedAccountCode,
Dimension,
SUM(ActAmtYtd) AS ActAmtYtd
FROM [xxxxxxxxxxxxxl] (@CurrentPeriod-$(a), -1, 0, 0, 0)
WHERE Dimension IN ('ActLcYtd', 'ActZarYtd')
AND Scenario = 'Act'
GROUP BY Period, CorpCode, CostCentre, CombinedAccountCode, Dimension
) p
PIVOT
(
SUM(ActAmtYtd) FOR Dimension IN ([ActLcYtd], [ActZarYtd])
) as pvt
;
Let a=a-1;
Loop
