<!-- --> > 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 ; > GO > > <pre> 1 rows affected > </pre> <!-- --> > create table gts > ( CCP char(4), > months int, > QUART int, > YEARS int, > GTS numeric(22,6) > ); > GO > > <pre> > ✓ > </pre> <!-- --> > 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) ; > GO > > <pre> 15 rows affected > </pre> <!-- --> > select > ccp, years, quart, > q1 = sum(gts) > from gts > group by ccp, years, quart ; > GO > > <pre> > 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 > </pre> <!-- --> > 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 ; > GO > > <pre> > ccp | years | quart | q2 > :--- | ----: | ----: | ---: > CCP1 | 2015 | 1 | 1 > CCP1 | 2015 | 2 | 18 > CCP1 | 2015 | 3 | 144 > CCP1 | 2015 | 4 | 864 > CCP1 | 2016 | 1 | 7776 > </pre> <!-- --> > 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 > ; > GO > > <pre> > 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 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2016&rdbms2=sqlserver_2016&fiddle=e0d042ae452d9c0121d7ca570807d9c6)*