sam - 1 year ago 28

SQL Question

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`

`SQL Server 2008 R2`

`to SQL Server 2012`

`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`

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.