title: SQL Server CTE Running balance
body: <p><strong>#table1</strong></p>
  
  <pre><code>idno | amount
  -------------
  1    | 700
  2    | 500
  </code></pre>
  
  <p><strong>#table2</strong></p>
  
  <pre><code>idno | amount1 | amount2 | amount3 | acctno
  ------------------------------------------
  1    | 100     | 200     | 300     | 001
  1    | 100     | 200     | 300     | 002
  2    | 100     | 200     | 300     | 001
  </code></pre>
  
  <p>What I want to happen is to distribute the amount from table2 into table 1's amount1,amount2,amount3 respectively then get the remaining balance and apply to the next row. I've tried using CTE but got stucked on passing the running balance to the next row. </p>
  
  <p><strong>Query:</strong></p>
  
  <pre><code>Declare @table2 TABLE (idno varchar(max), amount1 decimal,amount2 
  decimal,amount3 decimal,acctno varchar(max))
  INSERT INTO @table2 VALUES
  ('1',100,200,300,'001'),
  ('1',100,200,300,'002'),
  ('2',100,200,300,'001')
  Declare @table1 TABLE (idno varchar(max), amount decimal)
  INSERT INTO @table1 VALUES
  ('1',700),
  ('2',500);
  WITH due AS (SELECT a.idno,a.amount,b.acctno,b.amount1,b.amount2,b.amount3
                from @table1 a left join @table2 b on a.idno = b.idno),
        payment AS (SELECT *,case when amount-amount1&lt;0 then amount 
                else amount1 end as amount1pay
                ,case when amount-amount1&lt;=0 then 0 
                when amount-amount1-amount2 &lt;0 then amount-amount1
                else amount2 end as amount2pay ,
                case when amount-amount1-amount2&lt;=0 then 0 
                when amount-amount1-amount2-amount3&lt;0 
                then amount-amount1-amount2 else amount3 end as amount3pay
                FROM due),
        payment2 AS (SELECT SUM(amount-amount1pay-amount2pay-amount3pay) 
                     OVER ( PARTITION BY idno ORDER BY acctno
               ROWS  UNBOUNDED PRECEDING  ) as balance,* FROM payment)
  
  select * from payment2
  </code></pre>
  
  <p><strong>Current Result</strong></p>
  
  <pre><code>balance | idno | amount | acctno | amount1 | amount2 | amount3 | amount1pay | amount2pay | amount3pay
  ---------------------------------------------------------------------------------------------------------
    100   |    1 |    200 |   001  |     100 |     200 |    300  |    100     |    200     |    300
    200   |    1 |    200 |   002  |     100 |     200 |    300  |    100     |    200     |    300
      0   |    2 |    500 |   001  |     100 |     200 |    300  |    100     |    200     |    200
  </code></pre>
  
  <p><strong>Expected Result</strong></p>
  
  <pre><code>balance | idno | amount | acctno | amount1 | amount2 | amount3 | amount1pay | amount2pay | amount3pay
  ---------------------------------------------------------------------------------------------------------
    100   |    1 |    200 |   001  |     100 |     200 |    300  |    100     |    200     |    300
    100   |    1 |    200 |   002  |     100 |     200 |    300  |    100     |      0     |      0
      0   |    2 |    500 |   001  |     100 |     200 |    300  |    100     |    200     |    200
  </code></pre>