title: Running Multiplication in T-SQL
body: <h2>GTS Table</h2>
  
  <pre><code>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   
  </code></pre>
  
  <h2>Baseline table</h2>
  
  <pre><code>CCP BASELINE   YEARS    QUART
  ----  --------   -----  -----
  CCP1    5       2015    1
  </code></pre>
  
  <p><strong>Expected result</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p><a href="http://sqlfiddle.com/#!3/d78d2" rel="noreferrer"><strong>SQLFIDDLE</strong></a> </p>
  
  <p><strong>Explantion</strong></p>
  
  <p>Baseline table has single baseline value for each CCP. </p>
  
  <p>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. </p>
  
  <p>Here is a working query in <code>Sql Server 2008</code></p>
  
  <pre><code>;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;
  </code></pre>
  
  <p><strong>UPDATE :</strong></p>
  
  <p>To work with more than one year </p>
  
  <pre><code>;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; 
  </code></pre>
  
  <p>Now am looking for a solution in <code>Sql Server 2012+</code> which will utilize <code>SUM OVER(ORDER BY)</code> functionality or any better way</p>
  
  <p>Tried something like this </p>
  
  <pre><code>EXP(SUM(LOG(Baseline * GTS)) OVER (PARTITION BY CCP ORDER BY Years,Quart ROWS UNBOUNDED PRECEDING))
  </code></pre>
  
  <p>But didnt workout</p>