CCP months QUART YEARS GTS
---- ------ ----- ----- ---
CCP1 1 1 2015 5
CCP1 2 1 2015 6
CCP1 3 1 2015 7
CCP1 4 2 2015 4
CCP1 5 2 2015 2
CCP1 6 2 2015 2
CCP1 7 3 2015 3
CCP1 8 3 2015 2
CCP1 9 3 2015 1
CCP1 10 4 2015 2
CCP1 11 4 2015 3
CCP1 12 4 2015 4
CCP1 1 1 2016 8
CCP1 2 1 2016 1
CCP1 3 1 2016 3
CCP BASELINE YEARS QUART
---- -------- ----- -----
CCP1 5 2015 1
Expected result
CCP months QUART YEARS GTS result
---- ------ ----- ----- --- ------
CCP1 1 1 2015 5 25 -- 5 * 5 (here 5 is the baseline)
CCP1 2 1 2015 6 30 -- 6 * 5 (here 5 is the baseline)
CCP1 3 1 2015 7 35 -- 7 * 5 (here 5 is the baseline)
CCP1 4 2 2015 4 360 -- 90 * 4(25+30+35 = 90 is the basline)
CCP1 5 2 2015 2 180 -- 90 * 2(25+30+35 = 90 is the basline)
CCP1 6 2 2015 2 180 -- 90 * 2(25+30+35 = 90 is the basline)
CCP1 7 3 2015 3 2160.00 -- 720.00 * 3(360+180+180 = 720)
CCP1 8 3 2015 2 1440.00 -- 720.00 * 2(360+180+180 = 720)
CCP1 9 3 2015 1 720.00 -- 720.00 * 1(360+180+180 = 720)
CCP1 10 4 2015 2 8640.00 -- 4320.00
CCP1 11 4 2015 3 12960.00 -- 4320.00
CCP1 12 4 2015 4 17280.00 -- 4320.00
CCP1 1 1 2016 8 311040.00 -- 38880.00
CCP1 2 1 2016 1 77760.00 -- 38880.00
CCP1 3 1 2016 3 116640.00 -- 38880.00
Explantion
Baseline table has single baseline value for each CCP.
The baseline value should be applied to first quarter of each CCP and for the next quarters previous quarter sum value will be the basleine.
Here is a working query in Sql Server 2008
;WITH CTE AS
( SELECT b.CCP,
Baseline = CAST(b.Baseline AS DECIMAL(15,2)),
b.Years,
b.Quart,
g.Months,
g.GTS,
Result = CAST(b.Baseline * g.GTS AS DECIMAL(15,2)),
NextBaseline = SUM(CAST(b.Baseline * g.GTS AS DECIMAL(15, 2))) OVER(PARTITION BY g.CCP, g.years, g.quart),
RowNumber = ROW_NUMBER() OVER(PARTITION BY g.CCP, g.years, g.quart ORDER BY g.Months)
FROM #GTS AS g
INNER JOIN #Base AS b
ON B.CCP = g.CCP
AND b.QUART = g.QUART
AND b.YEARS = g.YEARS
UNION ALL
SELECT b.CCP,
CAST(b.NextBaseline AS DECIMAL(15, 2)),
b.Years,
b.Quart + 1,
g.Months,
g.GTS,
Result = CAST(b.NextBaseline * g.GTS AS DECIMAL(15,2)),
NextBaseline = SUM(CAST(b.NextBaseline * g.GTS AS DECIMAL(15, 2))) OVER(PARTITION BY g.CCP, g.years, g.quart),
RowNumber = ROW_NUMBER() OVER(PARTITION BY g.CCP, g.years, g.quart ORDER BY g.Months)
FROM #GTS AS g
INNER JOIN CTE AS b
ON B.CCP = g.CCP
AND b.Quart + 1 = g.QUART
AND b.YEARS = g.YEARS
AND b.RowNumber = 1
)
SELECT CCP, Months, Quart, Years, GTS, Result, Baseline
FROM CTE;
UPDATE :
To work with more than one year
;WITH order_cte
AS (SELECT Dense_rank() OVER(partition BY ccp ORDER BY years, quart) d_rn,*
FROM #gts),
CTE
AS (SELECT b.CCP,
Baseline = Cast(b.Baseline AS DECIMAL(15, 2)),
g.Years,
g.Quart,
g.Months,
g.GTS,
d_rn,
Result = Cast(b.Baseline * g.GTS AS DECIMAL(15, 2)),
NextBaseline = Sum(Cast(b.Baseline * g.GTS AS DECIMAL(15, 2)))
OVER(
PARTITION BY g.CCP, g.years, g.quart),
RowNumber = Row_number()
OVER(
PARTITION BY g.CCP, g.years, g.quart
ORDER BY g.Months)
FROM order_cte AS g
INNER JOIN #Baseline AS b
ON B.CCP = g.CCP
AND b.QUART = g.QUART
AND b.YEARS = g.YEARS
UNION ALL
SELECT b.CCP,
Cast(b.NextBaseline AS DECIMAL(15, 2)),
g.Years,
g.Quart,
g.Months,
g.GTS,
g.d_rn,
Result = Cast(b.NextBaseline * g.GTS AS DECIMAL(15, 2)),
NextBaseline = Sum(Cast(b.NextBaseline * g.GTS AS DECIMAL(15, 2)))
OVER(
PARTITION BY g.CCP, g.years, g.quart),
RowNumber = Row_number()
OVER(
PARTITION BY g.CCP, g.years, g.quart
ORDER BY g.Months)
FROM order_cte AS g
INNER JOIN CTE AS b
ON B.CCP = g.CCP
AND b.d_rn + 1 = g.d_rn
AND b.RowNumber = 1)
SELECT CCP,
Months,
Quart,
Years,
GTS,
Result,
Baseline
FROM CTE;
Now am looking for a solution in Sql Server 2012+ which will utilize SUM OVER(ORDER BY) functionality or any better way
Tried something like this
EXP(SUM(LOG(Baseline * GTS)) OVER (PARTITION BY CCP ORDER BY Years,Quart ROWS UNBOUNDED PRECEDING))
But didnt workout
Following solution assumes there are always 3 rows per quarter (only the last quarter might be partial), single SELECT, no recursion :-)
WITH sumQuart AS
(
SELECT *,
CASE
WHEN ROW_NUMBER() -- for the 1st month in a quarter
OVER (PARTITION BY CCP, Years, Quart
ORDER BY months) = 1
-- return the sum of all GTS of this quarter
THEN SUM(GTS) OVER (PARTITION BY CCP, Years, Quart)
ELSE NULL -- other months
END AS sumGTS
FROM gts
)
,cte AS
(
SELECT
sq.*,
COALESCE(b.Baseline, -- 1st quarter
-- product of all previous quarters
CASE
WHEN MIN(ABS(sumGTS)) -- any zeros?
OVER (PARTITION BY sq.CCP ORDER BY sq.Years, sq.Quart, sq.Months
ROWS BETWEEN UNBOUNDED PRECEDING AND 3 PRECEDING) = 0
THEN 0
ELSE -- product
EXP(SUM(LOG(NULLIF(ABS(COALESCE(b.Baseline,1) * sumGTS),0)))
OVER (PARTITION BY sq.CCP ORDER BY sq.Years, sq.Quart, sq.Months
ROWS BETWEEN UNBOUNDED PRECEDING AND 3 PRECEDING)) -- product
-- odd number of negative values -> negative result
* CASE WHEN COUNT(CASE WHEN sumGTS < 0 THEN 1 END)
OVER (PARTITION BY sq.CCP ORDER BY sq.Years, sq.Quart, sq.Months
ROWS BETWEEN UNBOUNDED PRECEDING AND 3 PRECEDING) % 2 = 0 THEN 1 ELSE -1 END
END) AS newBaseline
FROM sumQuart AS sq
LEFT JOIN BASELINE AS b
ON B.CCP = sq.CCP
AND b.Quart = sq.Quart
AND b.Years = sq.Years
)
SELECT
CCP, months, Quart, Years, GTS,
round(newBaseline * GTS,2),
round(newBaseline,2)
FROM cte
See Fiddle
EDIT: Added logic to handle values <= 0 Fiddle