Update value without cursor

Question

I have a table in the database.

Bill

ID Total Paid Status 1 1000 1000 Paid 2 500 400 Part Paid 3 700 0 Unpaid 4 200 0 Unpaid 

Now the user pays PAID_AMT -> $ 900, which I want to distribute so that my table looks like:

 ID Total Paid Status 1 1000 1000 Paid 2 500 500 Paid 3 700 700 Paid 4 200 100 Part Paid 

This can be easily done with the cursor, but I want to avoid the cursors.

Is it possible to achieve this using simple update requests with output parameters.

Something like that

 Update Bill Set Paid = Total, Status = 'Paid', Output PAID_AMT = PAID_AMT - (Total-Paid ) where Total-Paid > PAID_AMT 

Answer 1

The following query shows the amount owed to SQL Server 2012:

 select b.*, sum(total - paid) over (order by id) as cumNotPaid from bill b 

Now you can distribute the amount:

 select b.*, (case when cumNotPaid >= @AMOUNT then 0 when cumNotPaid - toBePaid <= @AMOUNT then toBePaid else @AMOUNT - cumnotPaid end) as PaidAmount from (select b.*, sum(total - paid) over (order by id) as cumNotPaid, total - paid as ToBePaid from bill b ) b 

Now it is an updatable CTE, so we can use this in the update instruction:

 with toupdate as ( (select b.*, (case when cumNotPaid >= @AMOUNT then 0 when cumNotPaid - toBePaid <= @AMOUNT then toBePaid else @AMOUNT - cumnotPaid end) as PaidAmount from (select b.*, sum(total - paid) over (order by id) as cumNotPaid, total - paid as ToBePaid from bill b ) b ) update toupdate set paid = PaidAmount, status = (case when total = paid then 'Paid' when total = 0 then 'UnPaid' else 'PartPaid' end); 

Answer 2

I do not know what version of SQL server you have, if it is 2008, then you could not use the current amount using the window function. You can try this recursive query:

 declare @paid_amount int = 900 ;with cte1 as ( select b.id, b.total - b.paid as diff, row_number() over(order by id) as row_num from Bill as b where b.total <> b.paid ), cte2 as ( select c1.id, c1.row_num, @paid_amount - c1.diff as paid_amount from cte1 as c1 where c1.row_num = 1 union all select c1.id, c1.row_num, c2.paid_amount - c1.diff as paid_amount from cte1 as c1 inner join cte2 as c2 on c2.row_num + 1 = c1.row_num where c2.paid_amount > 0 ) update Bill set Paid = case when c.paid_amount >= 0 then b.Total else b.Total - b.Paid + c.paid_amount end, Status = case when c.paid_amount >= 0 then 'Paid' else 'Part Paid' end from Bill as b inner join cte2 as c on c.id = b.id 

sql script demonstration

for SQL Server 2012 this is a little easier:

 declare @paid_amount int = 900 ;with cte1 as ( select b.id, b.total - b.paid as amount_to_pay, sum(b.total - b.paid) over(order by b.id) as amount from Bill as b where b.total <> b.paid ), cte2 as ( select c.id, @paid_amount - c.amount as remain_amount from cte1 as c where @paid_amount - c.amount + c.amount_to_pay >= 0 ) update Bill set Paid = case when c.remain_amount >= 0 then b.Total else b.Total - b.Paid + c.remain_amount end, Status = case when c.remain_amount >= 0 then 'Paid' else 'Part Paid' end from Bill as b inner join cte2 as c on c.id = b.id; 

sql script demonstration

Answer 3

If you are using SQL 2012, you can use the following:

 DECLARE @PayAmount INT = 900; WITH Dues AS ( SELECT *, Total-Paid AS Due FROM Bill ) , Cumulative AS ( SELECT *, SUM(Due) OVER (ORDER BY Id) AS CumulativeTotalDue FROM Dues ) , Payable AS ( SELECT *, @PayAmount - CumulativeTotalDue AS AmountLeftAfterPaying FROM Cumulative ) , BillWithAmountToApplyRaw AS ( SELECT * , CASE WHEN AmountLeftAfterPaying >= 0 THEN Due ELSE Due + AmountLeftAfterPaying END AS RawAmountToApply FROM Payable ) , BillWithAmountToApply AS ( SELECT *, CASE WHEN RawAmountToApply < 0 THEN 0 ELSE RawAmountToApply END AS AmountToApply FROM BillWithAmountToApplyRaw ) 

This will give you the amount to be applied in the AmountToApply column. So you can use the above as

 UPDATE BillWithAmountToApply SET Paid = Paid + AmountToApply FROM BillWithAmountToApply 

(Use

 SELECT * FROM BillWithAmountToApply 

to check it out if you want)

SQL 2008 version (less efficient due to repeated joins that are not needed in 2012):

 WITH Dues AS ( SELECT *, Total-Paid AS Due FROM Bill ) , CumulativeDue AS ( SELECT base.Id, SUM(cumulative.Due) AS CumulativeTotalDue FROM Dues base JOIN Dues cumulative ON cumulative.Id <= base.Id GROUP BY base.Id ) , Cumulative AS ( SELECT Dues.*, CumulativeDue.CumulativeTotalDue FROM Dues JOIN CumulativeDue ON CumulativeDue.Id = Dues.Id ) ... as above 

Schema Objects:

 --BEGIN TRAN; --CREATE TABLE Bill --( -- ID Int PRIMARY KEY IDENTITY, -- Total INT NOT NULL, -- Paid INT NOT NULL DEFAULT(0), -- Status AS -- CASE -- WHEN Paid = Total THEN 'Paid' -- WHEN Paid = 0 THEN 'Unpaid' -- ELSE 'Part Paid' -- END --); --WITH KnownValues(Total, Paid) AS --( -- SELECT 1000, 1000 -- UNION ALL SELECT 500, 400 -- UNION ALL SELECT 700, 0 -- UNION ALL SELECT 200, 0 --) --INSERT INTO Bill(Total, Paid) --SELECT * --FROM KnownValues; --COMMIT