Avoiding Cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Avoiding Cursor

Author

Topic

rillusioned
Starting Member

5 Posts

Posted - 2011-05-08 : 01:11:40

I have 2 tables Cost and Payment. I want the payments to be applied to the cost based on - 'earliest payment is applied first'.
Currently I use a cursor on the Payments table to loop through each row and update the two tables.

Is there a way this can be done through update queries without having to use cursors ??

Basically trying to figure out the most elegant way of updating both the tables / distributing the payments to the costs....
The 'Paid' column and 'AppliedAmt' column are updated in the 2 tables respectively.

See the 'Before' and 'After' data below.

***Before***

Cost Table:
CustomerId | Cost | Paid
100|100|0

Payment Table:
CustomerId| PayAmt| AppliedAmt| TransactionDate
100| 20| 0 | 2011-05-01
100| 90| 0 | 2011-05-02

***After***

Cost Table:
CustomerId| Cost| Paid
100| 100| 100

Payment Table:
CustomerId| PayAmt| AppliedAmt| TransactionDate
100| 20| 20| 2011-05-01
100| 90| 80| 2011-05-02

Thanks !

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-08 : 10:54:34

Here is one way to do it without using cursors.


-- TEST DATA
create table #Cost (CustomerId int, Cost int, Paid int);
create table #Payment( CustomerId int, PayAmt int, AppliedAmt int, TransactionDate date);

insert into #Cost values (100,100,0);
insert into #Payment values (100,20,0,'20110501'),(100,90,0,'20110502');

-- UPDATE PAYMENT TABLE
;with PayCTE as
(
   select
      *,
      row_number() over (partition by CustomerId order by TransactionDate) as pay_row
   from
      #Payment
),
TransCTE as
(
   select
      p.CustomerID,
      case when c.Cost <= p.PayAmt then 0 else c.Cost-p.PayAmt end as Balance,
      case when c.Cost <= p.PayAmt then c.Cost else p.PayAmt end as AppliedAmt,
      p.pay_row
   from
      PayCTE p
      inner join #Cost c on c.CustomerID = p.CustomerID
   where
      pay_row = 1
      
   union all
   
   select
      ct.CustomerId,
      case when ct.Balance <= p.PayAmt then 0 else ct.Balance-p.PayAmt end as Balance,
      case when ct.Balance <= p.PayAmt then ct.Balance else p.PayAmt end as AppliedAmt,
      p.pay_row
   from
      TransCTE ct
      inner join PayCTE p on p.CustomerId = ct.CustomerId and p.pay_row = ct.pay_row + 1
   where
      ct.Balance > 0
      
)update pc set
   AppliedAmt = tc.AppliedAmt
from
   PayCTE pc
   inner join TransCTE tc on tc.CustomerId = pc.CustomerId and tc.pay_row = pc.pay_row;

-- UPDATE COST TABLE
with sums as
(
   select
      p.CustomerId,
      sum(AppliedAmt) TotalPaid
   from
      #Payment p
   group by
      p.CustomerId
)
update c set
   Paid = TotalPaid
from
   #Cost c
   inner join sums s on s.CustomerId = c.CustomerId;
      
select * from #Cost;
select * from #Payment;

-- CLEANUP      
drop table #Cost;
drop table #Payment;

rillusioned
Starting Member

5 Posts

Posted - 2011-05-08 : 11:40:59

Nice. Something on the lines of what I was looking for...
You are awesome !!

rillusioned
Starting Member

5 Posts

Posted - 2011-05-09 : 17:58:41

Is there a way this can be extended for multiple cost rows ?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-09 : 18:02:14

Yes, I think that can be done, but just so I or someone else who may want to respond can understand exactly the requirement, can you post sample data and expected output, just like you posted in your original post?

rillusioned
Starting Member

5 Posts

Posted - 2011-05-09 : 18:15:37

This is what I would need:

***Before***

Cost Table:
CustomerId | Cost | Paid | Priority
100|100|0 | 1
100|50 |0 | 2
100|30 |0 | 3

Payment Table:
CustomerId| PayAmt| AppliedAmt| TransactionDate
100| 20| 0 | 2011-05-01
100| 90| 0 | 2011-05-02
100| 100| 0 | 2011-05-03
***After***

Cost Table:
CustomerId| Cost| Paid
100| 100| 100
100| 50 | 50
100| 30 | 30

Payment Table:
CustomerId| PayAmt| AppliedAmt| TransactionDate
100| 20| 20| 2011-05-01
100| 90| 90| 2011-05-02
100| 100| 70| 2011-05-02

Basically I want in the solution posted ...to continue to the next cost once the prior cost is met.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-09 : 20:23:17

It is getting complicateder and complicateder, but I think this should work; it does for your test data.


-- TEST DATA
drop table #Cost; drop table #Payment;
create table #Cost (CustomerId int, Cost int, Paid int, CostId int);
create table #Payment( CustomerId int, PayAmt int, AppliedAmt int, TransactionDate date);

insert into #Cost values (100,100,0,1),(100,50,0,2),(100,30,0,3);
insert into #Payment values (100,20,0,'20110501'),(100,90,0,'20110502'),(100,100,0,'20110503');

-- UPDATE PAYMENT TABLE
;with PayCTE as
(
   select
      *,
      row_number() over (partition by CustomerId order by TransactionDate) as pay_row
   from
      #Payment
),
CostCTE as
(
   select CustomerId, sum(Cost) as Cost from #Cost group by CustomerId
),
TransCTE as
(
   select
      p.CustomerID,
      case when c.Cost <= p.PayAmt then 0 else c.Cost-p.PayAmt end as Balance,
      case when c.Cost <= p.PayAmt then c.Cost else p.PayAmt end as AppliedAmt,
      p.pay_row
   from
      PayCTE p
      inner join CostCTE c on c.CustomerID = p.CustomerID
   where
      pay_row = 1
      
   union all
   
   select
      ct.CustomerId,
      case when ct.Balance <= p.PayAmt then 0 else ct.Balance-p.PayAmt end as Balance,
      case when ct.Balance <= p.PayAmt then ct.Balance else p.PayAmt end as AppliedAmt,
      p.pay_row
   from
      TransCTE ct
      inner join PayCTE p on p.CustomerId = ct.CustomerId and p.pay_row = ct.pay_row + 1
   where
      ct.Balance > 0
      
)update pc set
   AppliedAmt = tc.AppliedAmt
from
   PayCTE pc
   inner join TransCTE tc on tc.CustomerId = pc.CustomerId and tc.pay_row = pc.pay_row;

-- UPDATE COST TABLE

With AppliedCTE as
(
   select CustomerId, sum(AppliedAmt) as TotalApplied  from #Payment group by CustomerId
),
PaidCTE as
(
   select
      ac.CustomerId,
      case when ac.TotalApplied < c.Cost then 0 else ac.TotalApplied-c.Cost end as Remaining,
      case when ac.TotalApplied < c.Cost then ac.TotalApplied else c.Cost end as Paid,
      c.CostId
   from
      AppliedCTE ac
      inner join #Cost c on c.CustomerId = ac.CustomerId
   where
      c.CostId = 1
   union all
   
   select
      pc.CustomerId,
      case when pc.Remaining < c.Cost then 0 else pc.Remaining-c.Cost end as Remaining,
      case when pc.Remaining < c.Cost then pc.Remaining else c.Cost end as Paid,
      c.CostId
   from
      PaidCTE pc
      inner join #Cost c on c.CustomerId = pc.CustomerId and c.CostId = pc.CostId+1
   where
      pc.Remaining > 0
)
update c set
   Paid = pc.Paid
from
   #Cost c
   inner join PaidCTE pc on pc.CustomerId = c.CustomerId and pc.CostId = c.CostId;
      
select * from #Cost;
select * from #Payment;

-- CLEANUP      
drop table #Cost;
drop table #Payment;

Yeah, yeah, I know! "Complicateder" is not a real word, but it sounded funny when I said it out loud!

rillusioned
Starting Member

5 Posts

Posted - 2011-05-09 : 22:41:59

Thanks for the effort! My process actually needs the transaction level details too (payments associated with each cost and the corresponding amount)
The earlier solution created them in 'TransCTE'

..yeah is 'Complicateder' for me...not you I guess :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-10 : 07:33:53

Hm..... I am not getting it. The code I posted produces the correct output for your sample data. I also tried to put in a few other customerIds with various types of payment patterns - they all seem to work as I had programmed it in.

Obviously, I am missing something. Can you post some sample input data that demonstrates the problem along with the output you expect to get?

Thanks,
Sunita.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources