title: How to calculate total paid amount for each id in various stages and reducing the paid amount after each stage in SQL Server 2012
body: <p>I have two tables <code>TableA</code> and <code>TableB</code>. </p>
  
  <ul>
  <li><code>TableA</code> has total demand (column name -> amount) split into various rows. </li>
  <li><code>TableB</code> has supply numbers (column name -> amount). </li>
  </ul>
  
  <p>I want to split the supply numbers and assign them against each row in <code>TableA</code> for each id (column name -> number). </p>
  
  <p>Sample data for <code>TableA</code> and <code>TableB</code> are as follows:</p>
  
  <p><strong>TableA</strong>:</p>
  
  <pre><code>row number  amount
  -------------------
  1     x       10
  2     y        5
  3     z       120
  4     z        80
  5     z         5
  </code></pre>
  
  <p><strong>TableB</strong>:</p>
  
  <pre><code>number  amount
  ---------------
    x       5 
    y       15
    z       200
  </code></pre>
  
  <p>Required output is : </p>
  
  <pre><code>row number  amount  paid
  -------------------------
  1     x        10     5
  2     y         5     5
  3     z       120   120
  4     z        80    80
  5     z         5     0
  </code></pre>
  
  <p>As of now we are using the below mentioned code which is very ugly and does not give good performance as we are using recursive <code>cte</code>'s to do this job as our system was SQL Server 2008 R2 and we had no option but now our software has been upgraded to SQL Server 2012 and I know same can be achieved using sum function with order by in over clause. But I do not know how? </p>
  
  <pre><code>with cte as 
  (
      select 
          a.row, a.number, a.amount,
          b.amount as total
      from 
          tableA as a
      left join 
          tableB as b on a.number = b.number
  ),
  cte1 as 
  (
      select 
          row, number, amount, total,
          case 
             when amount - total &lt; 0 then amount else total 
          end as paid,
          case 
             when amount - total &lt; 0 then amount else total 
          end as totalpaid
      from 
          cte
      where 
          row = 1
  
      union all
  
      select 
          b.row, b.number, b.amount, b.total,
          case
             when b.amount - (b.total - (case when b.number = a.number then a.totalpaid else 0 end)) &lt; 0 then b.amount else (b.total - (case when b.number = a.number then a.totalpaid else 0 end)) end,
             case when b.amount - (b.total - (case when b.number = a.number then a.totalpaid else 0 end)) &lt; 0 then b.amount else (b.total - (case when b.number = a.number then a.totalpaid else 0 end)) end + ((case when b.number = a.number then a.totalpaid else 0 end))
      from 
          cte1 as a
      inner join 
          cte as b on b.row = a.row + 1
  )
  select 
      row, number, amount, paid
  from 
      cte1
  </code></pre>
  
  <p>Can someone tell me how to write the above code efficiently in SQL Server 2012? </p>
  
  <p>Thanks in advance.</p>