sam sam - 7 months ago 12
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
. Thanks in advance.

Answer

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;

And please give me feedback about correctness and performance improvement.