title: TSQL Recursive CTE That Contains a SUM
body: <p>I have a table called T that has three rows as follows....</p>
  
  <pre><code>Id   allocation   multiplier   multipliedallocation  multipliedallocationsum
  1    20.000       1.0008       20.016                100.052
  2    50.000       1.0006       50.030                100.052
  3    30.000       1.0002       30.006                100.052
  </code></pre>
  
  <p>I would like to use this table to produce some projection results that alter the allocation over a number of months as follows...</p>
  
  <pre><code>Id   allocation   multiplier   multipliedallocation  multipliedallocationsum  mnth
  1    20.000       1.0008       20.016                100.052                  1
  2    50.000       1.0006       50.030                100.052                  1
  3    30.000       1.0002       30.006                100.052                  1
  1    20.005       1.0008       20.021                100.050                  2
  2    50.003       1.0006       50.033                100.050                  2
  3    29.990       1.0002       29.996                100.050                  2
  1    20.011       1.0008       20.027                100.052                  3
  2    50.008       1.0006       50.038                100.052                  3
  3    29.981       1.0002       29.987                100.052                  3
  1    20.017       1.0008       20.033                100.054                  4
  2    50.012       1.0006       50.042                100.054                  4
  3    29.971       1.0002       29.979                100.054                  4
  
  etc
  </code></pre>
  
  <p>The multipliedallocation on any row = the allocation x multiplier.</p>
  
  <p>The multipliedallocationsum on any row = the sum of the multipliedallocation for that month</p>
  
  <p>The allocation for month n is the multipliedallocation for month n-1 divided by the multipliedallocationsum for month n-1, multiplied by 100</p>
  
  <p>This is the recursive cte SQL statement I have come up with so far, but it is not doing what I expect...</p>
  
  <pre><code>WITH Alloc (Id, allocation, multiplier, multipliedallocation, mnth)
  AS
  (
      SELECT Id, allocation, multiplier, allocation * multiplier AS multipliedallocation, 1 AS mnth
      FROM T
  ),
  AllocProjected (Id, allocation, multiplier, multipliedallocation, multipliedallocationsum, mnth)
  AS
  (
      SELECT a.Id, a.allocation, a.multiplier, a.multipliedallocation,
      SUM(a.multpliedallocation) OVER (PARTITION BY a.Id, a.mnth), 1
      FROM Alloc a
  
      UNION ALL
  
      SELECT a.Id,
      CONVERT(decimal(6,3), (a.multipliedallocation / a.multipliedallocationsum) * 100.0) AS allocation,
      a.multiplier AS multiplier,
      a.multiplier,
      a.multiplier * (a.multipliedallocation / a.multipliedallocationsum) * 100.0 AS multipliedallocation,
      SUM(a.multipliedallocation) OVER (PARTITION BY a.mnth) AS multipliedallocationsum,
      a.mnth + 1
      FROM AllocProjected a
      WHERE a.mnth &lt; 24
  )
  SELECT * FROM AllocProjected
  ORDER BY mnth
  </code></pre>
  
  <p>the multipliedallocationsum only seems to show multipliedallocation for a single row, not the sum of the multipliedallocation for the whole month (i.e. across 3 rows)</p>