Running Multiplication in T-SQL

Question

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 

Base 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 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 

SQLFIDDLE

Explantion

The base table has one base value for each CCP.

The base value should be applied to the first quarter of each CCP, and for the next quarter, the sum of the previous quarter will be a basque.

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 I am looking for a solution in Sql Server 2012+ that will use the functionality of SUM OVER(ORDER BY) 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 did not train

Answer 1

The following solution assumes that there are always 3 rows per quarter (only the last quarter can be partial), single SELECT, without 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 <= 0 Fiddle values

Answer 2

Another method that uses EXP(SUM(LOG())) tricks EXP(SUM(LOG())) and only window functions for the total number (without recursive CTE or cursors).

Tested with dbfiddle.uk :

 WITH ct AS ( SELECT ccp, years, quart, q2 = round(exp(coalesce(sum(log(sum(gts))) OVER (PARTITION BY ccp ORDER BY years, quart ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) , 0)) , 2) -- 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) b.baseline FROM baseline AS b WHERE b.ccp = ct.ccp ORDER BY b.years, b.quart ) AS b ; 

How it works:

• ( CREATE tables and INSERT skipped)

• 1 , allows you to group by ccp, year and quart and calculate the amounts:

  select ccp, years, quart, q1 = sum(gts) from gts group by ccp, years, quart ; GO 

 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

• 2 , we use the EXP(LOG(SUM()) trick to calculate the current multiplications of these sums. We use BETWEEEN .. AND -1 PRECEDING in the window to skip the current values, since these values ​​are used only for the baselines of the next quarter.
  Rounding should avoid inaccuracies when using LOG() and EXP() . You can experiment using ROUND() or casting in NUMERIC :

 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 

 ccp |  years |  quart |  q2
 : --- |  ----: |  ----: |  ---:
 CCP1 |  2015 |  1 |  one
 CCP1 |  2015 |  2 |  eighteen
 CCP1 |  2015 |  3 |  144
 CCP1 |  2015 |  4 |  864
 CCP1 |  2016 |  1 |  7776

• 3 , we combine the two queries in one (this is not necessary, it just makes the query more compact, you can use 2 CTEs instead), and then join gts so that we can multiply each value by the calculated q2 (which gives us a basic level )
  CROSS APPLY is just the baseline for each ccp.
  Note that I'm changing this text a bit to numeric(22,6) instead of rounding to two decimal places. The results are the same with the pattern, but they may differ if the numbers are larger or not integer:

 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 

 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

Answer 3

I'm not sure the question is how is the over() logic to either execute recursive cte in 2012 or just compute in 2012 without recursive cte.

It looks like you were trying to create the equivalent of agreggation PRODUCT() . But, as I added in my comment

The Exp function is not a valid window function and cannot be used with the OVER clause.

So, I made my version without recursive code for my answer. I include one additional year on a sample of data with different initial values

• Calculate Total gts For Each Quart
• Get a basic level with the first Quart
• Compute the baseline for another Quart executing dummy PRODUCT() aggregated

SQL Fiddle Demo

 WITH gtsTotal as ( SELECT [CCP], [Year], [QUART], SUM([GTS]) as sumGts FROM gts GROUP BY [CCP], [Year], [QUART] ), newBase as ( SELECT g.[CCP], g.[YEAR], b.[BASELINE], 1 as sQuart, b.[BASELINE] as [TotalBase] FROM gtsTotal g INNER JOIN baseline b on g.[Year] = b.[YEARS] and g.[CCP] = b.[CCP] WHERE g.[QUART] <= 1 UNION ALL SELECT g.[CCP], g.[YEAR], b.[BASELINE], MAX(g.[QUART]) + 1 as sQuart, (Exp(Sum(Log(sumGts))) * b.[BASELINE]) as [TotalBase] FROM gtsTotal g INNER JOIN baseline b on g.[Year] = b.[YEARS] and g.[CCP] = b.[CCP] WHERE g.[QUART] <= 1 GROUP BY g.[CCP], g.[YEAR], b.[BASELINE] UNION ALL SELECT g.[CCP], g.[YEAR], b.[BASELINE], MAX(g.[QUART]) + 1 as sQuart, (Exp(Sum(Log(sumGts))) * b.[BASELINE]) as [TotalBase] FROM gtsTotal g INNER JOIN baseline b on g.[Year] = b.[YEARS] and g.[CCP] = b.[CCP] WHERE g.[QUART] <= 2 GROUP BY g.[CCP], g.[YEAR], b.[BASELINE] UNION ALL SELECT g.[CCP], g.[YEAR], b.[BASELINE], MAX(g.[QUART]) + 1 as sQuart, (Exp(Sum(Log(sumGts))) * b.[BASELINE]) as [TotalBase] FROM gtsTotal g INNER JOIN baseline b on g.[Year] = b.[YEARS] and g.[CCP] = b.[CCP] WHERE g.[QUART] <= 3 GROUP BY g.[CCP], g.[YEAR], b.[BASELINE] ) SELECT g.CCP, g.months, g.QUART, g.Year, CEILING(g.GTS * n.TotalBase) FROM newBase n INNER JOIN gts g ON n.CCP = g.CCP AND n.[Year] = g.[Year] AND n.[sQuart] = g.[QUART] order by g.[Year], n.sQuart 

Exit

 | CCP | months | QUART | Year | Result| |------|--------|-------|------|-------| | CCP1 | 1 | 1 | 2015 | 25 | | CCP1 | 2 | 1 | 2015 | 30 | | CCP1 | 3 | 1 | 2015 | 35 | | CCP1 | 4 | 2 | 2015 | 360 | | CCP1 | 5 | 2 | 2015 | 180 | | CCP1 | 6 | 2 | 2015 | 180 | | CCP1 | 7 | 3 | 2015 | 2160 | | CCP1 | 8 | 3 | 2015 | 1440 | | CCP1 | 9 | 3 | 2015 | 720 | | CCP1 | 10 | 4 | 2015 | 8640 | | CCP1 | 11 | 4 | 2015 | 12960 | | CCP1 | 12 | 4 | 2015 | 17280 | 

Answer 4

Another alternative is to use stacked / cascaded CTE:

 ;WITH CteFirstQtr AS( SELECT g.*, result = b.BASELINE * g.GTS FROM gts g INNER JOIN BASELINE b ON b.CCP = g.CCP AND b.YEARS = g.YEARS AND b.QUART = g.QUART ), CteSecondQtr AS( SELECT g.*, result = t.result * g.gts FROM gts g CROSS APPLY( SELECT SUM(result) FROM CteFirstQtr WHERE CCP = g.CCP AND Years = g.Years AND QUART = 1 )t(result) WHERE g.QUART = 2 ), CteThirdQtr AS( SELECT g.*, result = t.result * g.gts FROM gts g CROSS APPLY( SELECT SUM(result) FROM CteSecondQtr WHERE CCP = g.CCP AND Years = g.Years AND QUART = 2 )t(result) WHERE g.QUART = 3 ), CteFourthQtr AS( SELECT g.*, result = t.result * g.gts FROM gts g CROSS APPLY( SELECT SUM(result) FROM CteThirdQtr WHERE CCP = g.CCP AND Years = g.Years AND QUART = 3 )t(result) WHERE g.QUART = 4 ) SELECT * FROM CteFirstQtr UNION ALL SELECT * FROM CteSecondQtr UNION ALL SELECT * FROM CteThirdQtr UNION ALL SELECT * FROM CteFourthQtr 

Answer 5

No recursion, no loops.

 DECLARE @BASELINE TABLE(CCP char(4), BASELINE numeric(22,6), YEARS int ,QUART int) DECLARE @gts TABLE (CCP char(4),months int,QUART int,YEARS int,GTS numeric(22,6)) insert into @baseline SELECT 'CCP1' AS CCP,5 AS BASELINE, 2015 AS YEARS,1 AS QUART insert into @gts (CCP,months,QUART,YEARS,GTS) SELECT 'CCP1' AS CCP,1 AS months,1 AS QUART ,2015 AS YEARS, 5 AS GTS UNION SELECT 'CCP1' AS CCP,2 AS months,1 AS QUART ,2015 AS YEARS, 6 AS GTS UNION SELECT 'CCP1' AS CCP,3 AS months,1 AS QUART ,2015 AS YEARS, 7 AS GTS UNION SELECT 'CCP1' AS CCP,4 AS months,2 AS QUART ,2015 AS YEARS, 4 AS GTS UNION SELECT 'CCP1' AS CCP,5 AS months,2 AS QUART ,2015 AS YEARS, 2 AS GTS UNION SELECT 'CCP1' AS CCP,6 AS months,2 AS QUART ,2015 AS YEARS, 2 AS GTS UNION SELECT 'CCP1' AS CCP,7 AS months,3 AS QUART ,2015 AS YEARS, 3 AS GTS UNION SELECT 'CCP1' AS CCP,8 AS months,3 AS QUART ,2015 AS YEARS, 2 AS GTS UNION SELECT 'CCP1' AS CCP,9 AS months,3 AS QUART ,2015 AS YEARS, 1 AS GTS UNION SELECT 'CCP1' AS CCP,10 AS months,4 AS QUART ,2015 AS YEARS, 2 AS GTS UNION SELECT 'CCP1' AS CCP,11 AS months,4 AS QUART ,2015 AS YEARS, 3 AS GTS UNION SELECT 'CCP1' AS CCP,12 AS months,4 AS QUART ,2015 AS YEARS, 4 AS GTS DECLARE @Summary TABLE (CCP char(4),[DATE] DATE,SumGTS numeric(22,6),AllPriorGTS numeric(22,6),[Multiplier] numeric(22,6)) INSERT INTO @Summary(CCP,[DATE],SumGTS) SELECT CCP,DATEADD(MONTH,(QUART-1)*3,DATEADD(YEAR,YEARS-2000,'01/01/2000')) [DATE] , SUM(GTS) [SumGTS] FROM @gts GROUP BY CCP,DATEADD(MONTH,(QUART-1)*3,DATEADD(YEAR,YEARS-2000,'01/01/2000')) UPDATE s SET AllPriorGTS= ( SELECT EXP(SUM(LOG(s1.SumGTS))) FROM @Summary s1 WHERE s1.[Date]<s.[DATE] ) from @Summary s UPDATE s SET [Multiplier]=SumGTS*ISNULL(AllPriorGTS,1) * bl.BASELINE from @Summary s INNER JOIN @baseline bl ON bl.CCP = s.CCP SELECT g.*,g.GTS*ISNULL(s.[Multiplier],bl.BASELINE) [RESULTS] FROM @gts g LEFT JOIN @Summary s ON DATEADD(MONTH,3,s.DATE)=DATEADD(MONTH,(g.QUART-1)*3,DATEADD(YEAR,g.YEARS-2000,'01/01/2000')) LEFT JOIN @baseline bl ON bl.CCP = g.CCP 

Answer 6

Without CTE. I suggest you work with months and years in the actual date format. I could become a little easier to manage the transition Years 2015 versus 2016, it becomes simple DATEADD (MM, -1, dateCol), and not calculate when to subtract or add a year. I did not implement this solution, but could help if you need it. This can lead to a fairly effective indexing strategy to speed up the query if you have a large dataset. I believe this can also be implemented using LAG.

 create table #tmp_BASELINE ( CCP char(4), BASELINE numeric(22,6), YEARS int , QUART int) create table #tmp_gts ( CCP char(4), months int, QUART int, YEARS int, GTS numeric(22,6) ) insert into #tmp_BASELINE SELECT 'CCP1' AS CCP,10 AS BASELINE, 2015 AS YEARS,1 AS QUART insert into #tmp_gts SELECT 'CCP1' AS CCP,1 AS months,1 AS QUART ,2015 AS YEARS, 50 AS GTS UNION SELECT 'CCP1' AS CCP,2 AS months,1 AS QUART ,2015 AS YEARS, 52 AS GTS UNION SELECT 'CCP1' AS CCP,3 AS months,1 AS QUART ,2015 AS YEARS, 57 AS GTS UNION SELECT 'CCP1' AS CCP,4 AS months,2 AS QUART ,2015 AS YEARS, 59 AS GTS UNION SELECT 'CCP1' AS CCP,5 AS months,2 AS QUART ,2015 AS YEARS, 61 AS GTS UNION SELECT 'CCP1' AS CCP,6 AS months,2 AS QUART ,2015 AS YEARS, 65 AS GTS UNION SELECT 'CCP1' AS CCP,7 AS months,3 AS QUART ,2015 AS YEARS, 69 AS GTS UNION SELECT 'CCP1' AS CCP,8 AS months,3 AS QUART ,2015 AS YEARS, 73 AS GTS UNION SELECT 'CCP1' AS CCP,9 AS months,3 AS QUART ,2015 AS YEARS, 78 AS GTS UNION SELECT 'CCP1' AS CCP,10 AS months,4 AS QUART ,2015 AS YEARS, 84 AS GTS UNION SELECT 'CCP1' AS CCP,11 AS months,4 AS QUART ,2015 AS YEARS, 90 AS GTS UNION SELECT 'CCP1' AS CCP,12 AS months,4 AS QUART ,2015 AS YEARS, 95 AS GTS SELECT * FROM #tmp_BASELINE SELECT CCP, Months, QUART, YEARS, GTS, SUM(GTS) OVER (PARTITION BY QUART) as QTRGTS, COALESCE((SELECT DISTINCT SUM(PGT.GTS) OVER (PARTITION BY QUART) FROM #tmp_gts as PGT WHERE GTS.YEARS = PGT.YEARS AND PGT.QUART = GTS.QUART-1),(SELECT TOP 1 BaseLine FROM #tmp_BASELINE)) as Modifier, GTS * COALESCE((SELECT DISTINCT SUM(PGT.GTS) OVER (PARTITION BY QUART) FROM #tmp_gts as PGT WHERE GTS.YEARS = PGT.YEARS AND PGT.QUART = GTS.QUART-1),(SELECT TOP 1 BaseLine FROM #tmp_BASELINE)) as GTSxModifier FROM #tmp_gts as GTS