gts table
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 baseline table
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 baseline) ccp1 2 1 2015 6 30 -- 6 * 5 (here 5 baseline) ccp1 3 1 2015 7 35 -- 7 * 5 (here 5 baseline) ccp1 4 2 2015 4 360 -- 90 * 4(25+30+35 = 90 basline) ccp1 5 2 2015 2 180 -- 90 * 2(25+30+35 = 90 basline) ccp1 6 2 2015 2 180 -- 90 * 2(25+30+35 = 90 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 each ccp.
the baseline value should applied first quarter of each ccp , next quarters previous quarter sum value basleine.
here working query in sql server 2008
;with cte ( select b.ccp, baseline = cast(b.baseline decimal(15,2)), b.years, b.quart, g.months, g.gts, result = cast(b.baseline * g.gts decimal(15,2)), nextbaseline = sum(cast(b.baseline * g.gts decimal(15, 2))) over(partition g.ccp, g.years, g.quart), rownumber = row_number() over(partition g.ccp, g.years, g.quart order g.months) #gts g inner join #base b on b.ccp = g.ccp , b.quart = g.quart , b.years = g.years union select b.ccp, cast(b.nextbaseline decimal(15, 2)), b.years, b.quart + 1, g.months, g.gts, result = cast(b.nextbaseline * g.gts decimal(15,2)), nextbaseline = sum(cast(b.nextbaseline * g.gts decimal(15, 2))) over(partition g.ccp, g.years, g.quart), rownumber = row_number() over(partition g.ccp, g.years, g.quart order g.months) #gts g inner join cte b on b.ccp = g.ccp , b.quart + 1 = g.quart , b.years = g.years , b.rownumber = 1 ) select ccp, months, quart, years, gts, result, baseline cte; update :
to work more 1 year
;with order_cte (select dense_rank() over(partition ccp order years, quart) d_rn,* #gts), cte (select b.ccp, baseline = cast(b.baseline decimal(15, 2)), g.years, g.quart, g.months, g.gts, d_rn, result = cast(b.baseline * g.gts decimal(15, 2)), nextbaseline = sum(cast(b.baseline * g.gts decimal(15, 2))) over( partition g.ccp, g.years, g.quart), rownumber = row_number() over( partition g.ccp, g.years, g.quart order g.months) order_cte g inner join #baseline b on b.ccp = g.ccp , b.quart = g.quart , b.years = g.years union select b.ccp, cast(b.nextbaseline decimal(15, 2)), g.years, g.quart, g.months, g.gts, g.d_rn, result = cast(b.nextbaseline * g.gts decimal(15, 2)), nextbaseline = sum(cast(b.nextbaseline * g.gts decimal(15, 2))) over( partition g.ccp, g.years, g.quart), rownumber = row_number() over( partition g.ccp, g.years, g.quart order g.months) order_cte g inner join cte b on b.ccp = g.ccp , b.d_rn + 1 = g.d_rn , b.rownumber = 1) select ccp, months, quart, years, gts, result, baseline cte; now looking solution in sql server 2012+ utilize sum over(order by) functionality or better way
tried
exp(sum(log(baseline * gts)) on (partition ccp order years,quart rows unbounded preceding)) but didnt workout
following solution assumes there 3 rows per quarter (only last quarter might partial), single select, no recursion :-)
with sumquart ( select *, case when row_number() -- 1st month in quarter on (partition ccp, years, quart order months) = 1 -- return sum of gts of quarter sum(gts) on (partition ccp, years, quart) else null -- other months end sumgts gts ) ,cte ( select sq.*, coalesce(b.baseline, -- 1st quarter -- product of previous quarters case when min(abs(sumgts)) -- zeros? on (partition sq.ccp order sq.years, sq.quart, sq.months rows between unbounded preceding , 3 preceding) = 0 0 else -- product exp(sum(log(nullif(abs(coalesce(b.baseline,1) * sumgts),0))) on (partition sq.ccp order sq.years, sq.quart, sq.months rows between unbounded preceding , 3 preceding)) -- product -- odd number of negative values -> negative result * case when count(case when sumgts < 0 1 end) on (partition sq.ccp order sq.years, sq.quart, sq.months rows between unbounded preceding , 3 preceding) % 2 = 0 1 else -1 end end) newbaseline sumquart sq left join baseline b on b.ccp = sq.ccp , b.quart = sq.quart , b.years = sq.years ) select ccp, months, quart, years, gts, round(newbaseline * gts,2), round(newbaseline,2) cte see fiddle
edit: added logic handle values <= 0 fiddle
Comments
Post a Comment