2 tables updating guidance - 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

General SQL Server Forums

New to SQL Server Programming

2 tables updating guidance

Author

Topic

kuyaglenn
Starting Member

4 Posts

Posted - 2009-12-02 : 13:45:20

I have 2 tables each basically have the same fields. One table i would call a Charges table and the other is the Payment table.
The fields are as follows for both tables:

Account #
Transaction Date
Due Date
Transaction Amount

Example Date to work with:

Charges Table:
Account,Transaction Date, Due Date, Trans Amount
1000,01/01/2009,02/01/2009,$100.00
1000,01/02/2009,02/02/2009,$150.00
1000,01/03/2009,02/03/2009,$450.00

Payment Table:
1000,03/01/2009,03/01/2009,$200.00
1000,03/02/2009,03/02/2009,$500.00

I want to look apply the payments to the tranactions, and once the payment is fully applied, either delete the transaction or zero out the amount. So for the first payment of $200.00 it will pay the first tranaction then partially pay the second. The second transaction will have a $50.00 outstanding balance.

How can i do this logic in SQL.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-03 : 07:17:47

Here's something to start us off...

--structure and data
declare @Charges table (Account int, TransactionDate datetime, DueDate datetime, TransAmount money)
insert @Charges
          select 1000, '01/01/2009', '02/01/2009', $100.00
union all select 1000, '01/02/2009', '02/02/2009', $150.00
union all select 1000, '01/03/2009', '02/03/2009', $450.00
union all select 1001, '01/03/2009', '02/03/2009', $10
--/

--inputs
declare @Account int, @Payment money
set @Account = 1000
set @Payment = $200.00
--/

--calculation
; with
  t1 as (select *, row_number() over (order by TransactionDate) as Row from @Charges where Account = @Account)
, t2 as (select a.Row, a.TransAmount, sum(b.TransAmount) as CumulativeTransAmount
         from t1 a inner join t1 b on a.Row >= b.Row group by a.Row, a.TransAmount)
, t3 as (select a.*, case when b.CumulativeTransAmount - @Payment < 0 then 0
                          when b.CumulativeTransAmount - @Payment < b.TransAmount then b.CumulativeTransAmount - @Payment
                     else b.TransAmount end as NewTransAmount
         from t1 a inner join t2 b on a.Row = b.Row)
update t3 set TransAmount = NewTransAmount where TransAmount != NewTransAmount
--/

--results
select * from @Charges
/*
Account  TransactionDate  DueDate     TransAmount
-------- ---------------- ----------- ---------------------
1000     2009-01-01       2009-01-02  0.00
1000     2009-02-01       2009-02-02  50.00
1000     2009-03-01       2009-03-02  450.00
1001     2009-03-01       2009-03-02  10.00
*/
--/

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

kuyaglenn
Starting Member

4 Posts

Posted - 2009-12-05 : 22:51:19

Wow awesome code and works as i wanted. Now i'm just a beginner in sql and how would i loop through accounts? Here's what i have so far and i find with a large database it takes forever.
How can i get rid of the cursor and use something more efficient?
----------
DECLARE PAYMENT_CURSOR CURSOR FAST_FORWARD FOR
SELECT dROLLNMBR,dGRDCD,dYEAR FROM #PAYMENTAPPLY GROUP BY dROLLNMBR,dGRDCD,dYEAR
OPEN PAYMENT_CURSOR
DECLARE @ROLLNMBR char(15)
DECLARE @GRDCD char(3)
DECLARE @YEAR smallint
FETCH NEXT FROM PAYMENT_CURSOR INTO @ROLLNMBR,@GRDCD,@YEAR
WHILE(@@FETCH_STATUS=0)
BEGIN
set @Payment = (select SUM(dAPPLYAMOUNT)as Payment from #PAYMENTAPPLY where dROLLNMBR=@ROLLNMBR and dGRDCD = @GRDCD and dYear=@YEAR)
; with
t1 as (select *, row_number() over (order by dTRXDATE) as Row from #TRANGRID where dROLLNMBR = @ROLLNMBR and dGRDCD = @GRDCD and dYear=@YEAR )
, t2 as (select a.Row, a.dAPPLYAMOUNT, sum(b.dAPPLYAMOUNT) as CumulativeTransAmount
from t1 a inner join t1 b on a.Row >= b.Row group by a.Row, a.dAPPLYAMOUNT)
, t3 as (select a.*, case when b.CumulativeTransAmount - @Payment < 0 then 0
when b.CumulativeTransAmount - @Payment < b.dAPPLYAMOUNT
then b.CumulativeTransAmount - @Payment
else b.dAPPLYAMOUNT end as NewTransAmount
from t1 a inner join t2 b on a.Row = b.Row)
update t3 set dAPPLYAMOUNT = NewTransAmount where dAPPLYAMOUNT != NewTransAmount
FETCH NEXT FROM PAYMENT_CURSOR INTO @ROLLNMBR,@GRDCD,@YEAR
END
CLOSE PAYMENT_CURSOR
DEALLOCATE PAYMENT_CURSOR

kuyaglenn
Starting Member

4 Posts

Posted - 2009-12-10 : 21:03:54

Please can anyone suggest to me how i can do the above solution that RyanRandall provided for more than one account? The cursor method is painfully slow.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-11 : 12:02:48

quote:
Originally posted by kuyaglenn

Please can anyone suggest to me how i can do the above solution that RyanRandall provided for more than one account? The cursor method is painfully slow.

the solution Ryan suggested doesnot use cursor, it makes use of common table expression. Not sure why you need to loop through records. what you happened when you tried code provided?

kuyaglenn
Starting Member

4 Posts

Posted - 2009-12-11 : 12:41:49

It works awesome for one account. But i need to tweak it to handle for many accounts. That's why i used the cursor.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-11 : 12:46:27

quote:
Originally posted by kuyaglenn

It works awesome for one account. But i need to tweak it to handle for many accounts. That's why i used the cursor.

ok in that case just remove first where condition inside t1 where Account = @Account

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources