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 such that my table looks:

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

It can be easily done using cursor, but i want to avoid cursors.

Is it possible to achieve this using simple update queries with Output parameters.

Something like this

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, assuming SQL Server 2012:

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

You can now 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, this is an updatable CTE, so we can use this in an update statement:

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 don't know what version of SQL server do you, if it 2008 then you could not use rolling sum with 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 fiddle demo

for SQL server 2012 it's a bit 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 fiddle demo

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
)

That will give you the amount to apply 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 first if you want)

SQL 2008 Version (less efficient because of the repeated joins, which aren't necessary 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