sam - 2 years ago 63
SQL Question

# How to calculate total paid amount for each id in various stages and reducing the paid amount after each stage in sql server 2012

I have two tables TableA and TableB. TableA has total demand (column name - > amount) split into various rows. TableB has supply numbers (coulumn name - > amount). I want to split the supply numbers and assign them against each row in TableA for each id (Coulumn name - > number). The sample data for TableA and TableB are as follows:

TableA:

``````row number  amount
1     x       10
2     y        5
3     z       120
4     z        80
5     z         5
``````

TableB :

``````number  amount
x       5
y       15
z       200
``````

Required output is :

``````row number  amount  paid
1     x       10    5
2     y        5    5
3     z       120   120
4     z        80   80
5     z         5   0
``````

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
`cte`
'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?

``````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 < 0 then amount else total end as paid,
case when amount - total < 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)) < 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)) < 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
``````

Can someone tell me how to write the above code efficiently in
`SQL Server 2012`

Try this code:

``````WITH cte as
(
SELECT a.row,
a.number,
a.amount,
b.amount AS totalPaid,
SUM(a.amount) OVER (PARTITION BY a.number ORDER BY row ROWS UNBOUNDED PRECEDING) AS totalAmount
FROM (VALUES (1,'x',10),(2,'y',5),(3,'z',120),(4,'z',80),(5,'z',5)) AS a(row, number, amount)
LEFT JOIN (VALUES ('x',5),('y',15),('z',200)) as b(number, amount) ON a.number = b.number
)
SELECT row, number, amount, CASE WHEN totalPaid >= totalAmount THEN amount ELSE CASE WHEN amount - totalAmount + totalPaid < 0 THEN 0 ELSE amount - totalAmount + totalPaid END END AS paid
FROM cte;
``````