clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2555272 fiddles created (37416 in the last week).

create table BASELINE (CCP char(4), BASELINE numeric(22,6), YEARS int ,QUART int); insert into baseline SELECT 'CCP1' AS CCP,5 AS BASELINE, 2015 AS YEARS,1 AS QUART ;
1 rows affected
 hidden batch(es)


create table gts ( CCP char(4), months int, QUART int, YEARS int, GTS numeric(22,6) );
 hidden batch(es)


insert into gts (ccp, months, quart, years, gts) values ('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) ;
15 rows affected
 hidden batch(es)


select ccp, years, quart, q1 = sum(gts) from gts group by ccp, years, quart ;
ccp years quart q1
CCP1 2015 1 18.000000
CCP1 2015 2 8.000000
CCP1 2015 3 6.000000
CCP1 2015 4 9.000000
CCP1 2016 1 12.000000
 hidden batch(es)


with ct as ( select ccp, years, quart, q1 = sum(gts) from gts group by ccp, years, quart ) select ccp, years, quart, -- months, gts, q1, q2 = round(exp(coalesce(sum(log(q1)) OVER (PARTITION BY ccp ORDER BY Years, Quart ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0)),2) from ct ;
ccp years quart q2
CCP1 2015 1 1
CCP1 2015 2 18
CCP1 2015 3 144
CCP1 2015 4 864
CCP1 2016 1 7776
 hidden batch(es)


with ct as ( select ccp, years, quart, q2 = cast(exp(coalesce(sum(log(sum(gts))) OVER (PARTITION BY ccp ORDER BY years, quart ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) , 0.0)) as numeric(22,6)) -- round appropriately to your requirements from gts group by ccp, years, quart ) select g.*, result = g.gts * b.baseline * ct.q2, baseline = b.baseline * ct.q2 from ct join gts as g on ct.ccp = g.ccp and ct.years = g.years and ct.quart = g.quart cross apply ( select top (1) baseline from baseline as b where b.ccp = ct.ccp order by years, quart ) as b ;
CCP months QUART YEARS GTS result baseline
CCP1 1 1 2015 5.000000 25.000000 5.000000
CCP1 2 1 2015 6.000000 30.000000 5.000000
CCP1 3 1 2015 7.000000 35.000000 5.000000
CCP1 4 2 2015 4.000000 360.000000 90.000000
CCP1 5 2 2015 2.000000 180.000000 90.000000
CCP1 6 2 2015 2.000000 180.000000 90.000000
CCP1 7 3 2015 3.000000 2160.000000 720.000000
CCP1 8 3 2015 2.000000 1440.000000 720.000000
CCP1 9 3 2015 1.000000 720.000000 720.000000
CCP1 10 4 2015 2.000000 8640.000000 4320.000000
CCP1 11 4 2015 3.000000 12960.000000 4320.000000
CCP1 12 4 2015 4.000000 17280.000000 4320.000000
CCP1 1 1 2016 8.000000 311040.000000 38880.000000
CCP1 2 1 2016 1.000000 38880.000000 38880.000000
CCP1 3 1 2016 3.000000 116640.000000 38880.000000
 hidden batch(es)