Update Value without cursor

azure-sql-database, sql, sql-server

Solution

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);

Problem

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

Original source