I am currently using a cursor in my sql server procedure. Wanted to know if there is anyway to replace it with a better approach. Process is
How can I remove the cursors for a more effective way in steps 2 and 3. Also does use of cursors mean that the PAYMENT and BILL tables remain locked till the procedure runs?
Tx
Here's one way it can be done, with made up tables and data since we don't know what yours look like. I'm putting some narrative in in places but all of the code should be run as one single script.
Data setup:
declare @bills table (billid int, balance decimal(38,4))
declare @payments table (paymentid int, balance decimal(38,4))
insert into @bills (billid, balance) values
(1,0), (2,22.50), (3,12.75), (4,19.20)
insert into @payments (paymentid,balance) values
(1,20.19),(2,5.50),(3,20)
declare @newpayments table (billid int, paymentid int,
paymentamount decimal(38,4))
I've assumed that the bills and payments tables have a column, called balance which shows any amounts not dealt with as yet. Alternatively, you may have to calculate this from a couple of columns. But no sample data in your question means I get to make up an easy structure :-)
Query to populate @newpayments with which bills should be paid from which (partial) payments1:
; With unpaidbills as (
select billid,balance,
ROW_NUMBER() OVER (ORDER BY billid) as rn,
SUM(balance) OVER (ORDER BY billid
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) as endbalance,
SUM(balance) OVER (ORDER BY billid
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) - balance as startbalance
from @bills
where balance > 0
), unusedpayments as (
select paymentid,balance,
ROW_NUMBER() OVER (ORDER BY paymentid) as rn,
SUM(balance) OVER (ORDER BY paymentid
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) as endbalance,
SUM(balance) OVER (ORDER BY paymentid
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) - balance as startbalance
from @payments
where balance > 0
), overlaps as (
select
billid,paymentid,
CASE WHEN ub.startbalance < up.startbalance
THEN up.startbalance ELSE ub.startbalance END as overlapstart,
CASE WHEN ub.endbalance > up.endbalance
THEN up.endbalance ELSE ub.endbalance END as overlapend
from
unpaidbills ub
inner join
unusedpayments up
on
ub.startbalance < up.endbalance and
up.startbalance < ub.endbalance
)
insert into @newpayments(billid,paymentid,paymentamount)
select billid,paymentid,overlapend - overlapstart as paymentamount
from overlaps
At this point, @newpayments can be used to generate transaction history, etc
And then, finally we update the original tables to mark the amounts used:
;With totalpaid as (
select billid,SUM(paymentamount) as payment from @newpayments
group by billid
)
update b
set b.balance = b.balance - tp.payment
from @bills b
inner join
totalpaid tp
on b.billid = tp.billid
;With totalused as (
select paymentid,SUM(paymentamount) as payment from @newpayments
group by paymentid
)
update p
set p.balance = p.balance - tu.payment
from @payments p
inner join
totalused tu
on p.paymentid = tu.paymentid
The key part was to use SUM() with window functions to calculate the running totals of the amounts owed (bills) or amounts available (payments), in both cases using a column (billid or paymentid) to determine in what order each of these items should be dealt with. E.g. the unpaidbills CTE produces a result set like this:
billid balance rn endbalance startbalance
----------- --------- -------------------- ------------- -------------
2 22.5000 1 22.5000 0.0000
3 12.7500 2 35.2500 22.5000
4 19.2000 3 54.4500 35.2500
and unusedpayments looks like this:
paymentid balance rn endbalance startbalance
----------- ---------- -------------------- ------------ -------------
1 20.1900 1 20.1900 0.0000
2 5.5000 2 25.6900 20.1900
3 20.0000 3 45.6900 25.6900
We then create the overlaps CTE which finds overlaps2 between the bills and payments where (part of) a payment can be used to satisfy (part of) a bill. The region of the overlap is the actual amount to pay for that bill.
1 The ROW_NUMBER() calls aren't really needed. In an early part of writing this query, I thought I was going to use these but it turned out to be unnecessary. But removing them doesn't shorten things enough to allow SO to stop scrolling that query anyway, and so I may as well leave them in (and not have to edit the result sets shown lower down also)
2 Many people trying to find overlaps make things absurdly complicated and deal with many special cases to find all overlaps. This can usually be done far more simply in the way that I show in the overlaps CTE - two ranges overlap if the first range starts before the second range ends, and the second range starts before the first range ends.
The only tricky thing to do is to decide whether you want to deal with two ranges that abut (the first one's end value is exactly equal to the second one's start or vice versa) but that just leads to a decision on whether to use < or <= in the comparisons.
In this instance, we don't care if a payment exactly paid off the previous bill so we use < to avoid treating such situations as an overlap.