update 2 tables at once

Question

Post reply

update 2 tables at once

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

dbaltazar (2/25/2010)
The sample data already given is realistic but I'll work on getting more.

This would be appreciated.

AcctID+FundID are the key between the 2 tables.

Okay, understood.

I want to loop thru payment looking into pledge and say ok, here's the first payment I find...

And I'd like to develop a set-based solution for you that doesn't involve looping... forget about trying to process one row at a time, if you can process all of them at once it will be a lot better.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ray Dai SSCommitted Points: 1548 More actions · Answer 2

Try this, not sure it handles all of your scenarios, so more sample data would be good. But it is working for what you given us.

declare @Pledge table (acctID int, [date] datetime, fundid int, Pledged numeric(8,2),Received numeric(8,2) DEFAULT (0), id int, cumlativeamount numeric(8,2)) --added cumlativeamount field

insert into @Pledge (acctID, [date], fundid, Pledged, Received, id )

select 1, '2/1/2010', 1, 100.00, 0,500 UNION ALL

select 5, '10/20/1999', 30, 500.00,0, 501 UNION ALL

select 55, '1/15/1997', 501, 500.00, 0,502 UNION ALL

select 400, '5/18/2009', 451, 10.00,0,503 UNION ALL

select 1, '2/1/2010', 1, 100.00, 0,503

declare @Payment table (paymentid int identity (1,1),acctID int, [date] datetime, fundid int, amount numeric(8,2), pledgeID int, cumlativeamount numeric(8,2)) --added paymenteid identity column and cumlativeamount

insert into @Payment (acctid, [date], fundid, amount)

select 1, '2/1/2010', 1, 75.00 UNION ALL

select 400, '5/30/2009', 451, 1.00 UNION ALL

select 5, '10/20/1999', 30, 300.00 UNION ALL

select 1, '2/1/2010', 1, 25.00 UNION ALL

select 1, '2/1/2010', 1, 100.00 UNION ALL

select 400, '6/15/2009', 451, 1.00 UNION ALL

select 400, '6/30/2009', 451, 1.00 UNION ALL

select 400, '7/15/2009', 451, 1.00 UNION ALL

select 400, '7/30/2009', 451, 1.00 UNION ALL

select 400, '8/15/2009', 451, 5.00

--Before picture--

select * from @pledge

select * from @payment

update P

set P.cumlativeamount = (select sum(P2.amount) from @payment P2 where --find cumlative sum of payments including the current one for the given acctid, fundid

P.acctid = p2.acctid

and p.fundid = p2.fundid

and P2.paymentid <= P.paymentid

and P2.date <= P.date

)

from @payment P

update P

set P.cumlativeamount = (select sum(P2.pledged) from @pledge P2 where --find cumlative sum of pledges including the current one for the given acctid, fundid

P.acctid = p2.acctid

and p.fundid = p2.fundid

and P2.id <= P.id

and P2.date <= P.date

)

from @pledge P

--setting pledgeid--

--if cumlative amount of payments (all the payments so far) is < the cumlative amount of 1st pledge

--then that payment will get this pledge id, up till the point they are equal

--then the (next payment+all the payments for the last pledge) will have a cumlative amount > the 1st pledge, then it is time to move on to the next pledge

update P

set P.pledgeid = (select top 1 PP.id from @pledge PP where P.cumlativeamount <= PP.cumlativeamount and PP.fundid=P.fundid and P.Acctid=pp.acctid)

from @payment P

update PP

set PP.Received = (select sum(Amount) from @Payment P where PP.fundid=P.fundid and P.Acctid=pp.acctid and PP.ID = P.pledgeid)

from @Pledge PP

--after picture--

select * from @pledge

select * from @payment

dbaltazar SSC Eights! Points: 884 More actions · Answer 3

Ok, here is some good sample data. I have some here where an acct made more than 1 pledge on the same day with the same fund. There is 1 pledge here where no payment has been made. One pledge where payments have been made but the pledge is not fulfilled. (that's ok) and there is 1 pledge that is unique that is fulfilled by its payments. All scenarios except an overpayment which I don't need to worry about.

declare @Pledge table (acctID int, [date] datetime, fundid int, Pledged numeric(8,2),Received numeric(8,2) DEFAULT (0), id int)

insert into @Pledge (acctID, [date], fundid, Pledged, Received, id )

select 1, '2/1/2010', 1, 100.00, 0,500 UNION ALL

select 5, '10/20/1999', 30, 500.00,0, 501 UNION ALL

select 55, '1/15/1997', 501, 500.00, 0,502 UNION ALL

select 400, '5/18/2009', 451, 10.00,0,503 UNION ALL

select 1, '2/1/2010', 1, 100.00, 0,503

declare @Payment table (acctID int, [date] datetime, fundid int, amount numeric(8,2), pledgeID int)

insert into @Payment (acctid, [date], fundid, amount)

select 1, '2/1/2010', 1, 75.00 UNION ALL

select 400, '5/30/2009', 451, 1.00 UNION ALL

select 5, '10/20/1999', 30, 300.00 UNION ALL

select 1, '2/1/2010', 1, 25.00 UNION ALL

select 1, '2/1/2010', 1, 100.00 UNION ALL

select 400, '6/15/2009', 451, 1.00 UNION ALL

select 400, '6/30/2009', 451, 1.00 UNION ALL

select 400, '7/15/2009', 451, 1.00 UNION ALL

select 400, '7/30/2009', 451, 1.00 UNION ALL

select 400, '8/15/2009', 451, 5.00

Always payments will have more records than pledges. We are not talking more than 500,000 records in either pledge or payment table. Atleast at this point.

The join between pledge (1) to payments (many) is acctID+FundID.

Pledge.Received needs to be <= sum(Payment.Amount) for an acctID+FundID - this way we can calculate the amt. paid to figure out balance owed.

Payment.PledgeID needs = Pledge.ID - this way we know the payment was used and we know which pledge it was used against. It would be very bad to use a payment more than once.

Ray Dai SSCommitted Points: 1548 More actions · Answer 4

Please try my modified code above, now with your data and added a fix for updating the received amount on Pledge table.

dbaltazar SSC Eights! Points: 884 More actions · Answer 5

ray-SQL (2/25/2010)
Please try my modified code above, now with your data and added a fix for updating the received amount on Pledge table.

Thank you

I'll give it a try. I'm traveling for business the next week but will try it out and let you know. If I don't respond right away that's why. I think it's rude when someone takes the time to help and they don't find out what happened. So I'll let you know. Thanks again

Ray Dai SSCommitted Points: 1548 More actions · Answer 6

Ray Dai

SSCommitted

Points: 1548

February 25, 2010 at 2:49 pm

#1125237

Sounds good. Have a good trip.

WayneS SSC Guru Points: 95460 More actions · Answer 7

ray-SQL (2/25/2010)
Try this, not sure it handles all of your scenarios, so more sample data would be good. But it is working for what you given us.

It's looking good to me. I was trying to figure out a simpler way to do it, but I haven't yet.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ray Dai SSCommitted Points: 1548 More actions · Answer 8

Thanks. I thought about somehow use the OUTPUT on the UPDATE but this seems too complicated for that.

WayneS SSC Guru Points: 95460 More actions · Answer 9

ray-SQL (2/25/2010)
Try this, not sure it handles all of your scenarios, so more sample data would be good. But it is working for what you given us.

I've modified what you provided. This does not require the three extra columns in the table, and ends up performing just 2 updates instead of 4. (The three columns are derived in CTEs.) What do you think of this?

declare @Pledge table (acctID int, [date] datetime, fundid int, Pledged numeric(8,2),Received numeric(8,2) DEFAULT (0), id int)

insert into @Pledge (acctID, [date], fundid, Pledged, Received, id )

select 1, '2/1/2010', 1, 100.00, 0,500 UNION ALL

select 5, '10/20/1999', 30, 500.00,0, 501 UNION ALL

select 55, '1/15/1997', 501, 500.00, 0,502 UNION ALL

select 400, '5/18/2009', 451, 10.00,0,503 UNION ALL

select 1, '2/1/2010', 1, 100.00, 0,504 -- <<<< NOTE! Changed this to the next number

declare @Payment table (acctID int, [date] datetime, fundid int, amount numeric(8,2), pledgeID int)

insert into @Payment (acctid, [date], fundid, amount)

select 1, '2/1/2010', 1, 75.00 UNION ALL

select 400, '5/30/2009', 451, 1.00 UNION ALL

select 5, '10/20/1999', 30, 300.00 UNION ALL

select 1, '2/1/2010', 1, 25.00 UNION ALL

select 1, '2/1/2010', 1, 100.00 UNION ALL

select 400, '6/15/2009', 451, 1.00 UNION ALL

select 400, '6/30/2009', 451, 1.00 UNION ALL

select 400, '7/15/2009', 451, 1.00 UNION ALL

select 400, '7/30/2009', 451, 1.00 UNION ALL

select 400, '8/15/2009', 451, 5.00

--Before picture--

select * from @pledge

select * from @payment

--setting pledgeid--

--if cumlative amount of payments (all the payments so far) is < the cumlative amount of 1st pledge

--then that payment will get this pledge id, up till the point they are equal

--then the (next payment+all the payments for the last pledge) will have a cumlative amount > the 1st pledge, then it is time to move on to the next pledge

;WITH Payment AS

(

select acctID, date, fundID, amount, pledgeID,

-- simulate an identity column with the row_number function

paymentid = ROW_NUMBER() OVER (ORDER by pledgeID)

from @Payment

), Payment2 AS

(

select acctID, date, fundID, amount, pledgeID,

cumulative = (select sum(P2.Amount)

from Payment P2 --find cumlative sum of payments including the current one for the given acctid, fundid

where P.acctid = p2.acctid

and p.fundid = p2.fundid

and P2.paymentid <= P.paymentid

and P2.date <= P.date)

from Payment P

), Pledge AS

(

select acctID, date, fundID, Pledged, Received, id,

cumulative = (select sum(p2.pledged)

from @pledge P2 --find cumlative sum of pledges including the current one for the given acctid, fundid

where P.acctid = p2.acctid

and p.fundid = p2.fundid

and P2.id <= P.id

and P2.date <= P.date)

from @pledge P

)

update P

set P.pledgeid = (select top 1 PP.id

from Pledge PP

where P.cumulative <= PP.cumulative

and PP.fundid=P.fundid

and P.Acctid=pp.acctid)

from Payment2 P

update PP

set PP.Received = (select sum(Amount)

from @Payment P

where PP.fundid=P.fundid

and P.Acctid=pp.acctid

and PP.ID = P.pledgeid)

from @Pledge PP

--after picture--

select * from @pledge

select * from @payment

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ray Dai SSCommitted Points: 1548 More actions · Answer 10

Nice,

Those CTEs are great and this might work better for him since he might not have the rights to modify the tables.

WayneS SSC Guru Points: 95460 More actions · Answer 11

Exactly what I was thinking. (also didn't like 4 updates going on...)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004310 More actions · Answer 12

Always payments will have more records than pledges.

Gosh... that may be your requirement, but I really find it hard to believe that there will never be an instance where someone doesn't make 2 pledges and make a single payment to cover both. For example, this comes up with the wrong answer if such a thing were to actually occur...

DROP TABLE #Pledge,#Payment

GO

CREATE table #Pledge (acctID int, [date] datetime, fundid int, Pledged numeric(8,2),Received numeric(8,2) DEFAULT (0), id int, cumlativeamount numeric(8,2))

insert into #Pledge (acctID, [date], fundid, Pledged, Received, id )

select 1, '2/1/2010', 1, 100.00, 0,500 UNION ALL

select 5, '10/20/1999', 30, 500.00,0, 501 UNION ALL

select 55, '1/15/1997', 501, 500.00, 0,502 UNION ALL

select 400, '5/18/2009', 451, 10.00,0,503 UNION ALL

select 1, '2/1/2010', 1, 100.00, 0,503

CREATE table #Payment(paymentid int identity (1,1),acctID int, [date] datetime, fundid int, amount numeric(8,2), pledgeID int, cumlativeamount numeric(8,2))

insert into #Payment (acctid, [date], fundid, amount)

--select 1, '2/1/2010', 1, 75.00 UNION ALL --Removed

select 400, '5/30/2009', 451, 1.00 UNION ALL

select 5, '10/20/1999', 30, 300.00 UNION ALL

--select 1, '2/1/2010', 1, 20.00 UNION ALL --CHANGED FROM 25

--select 1, '2/1/2010', 1, 90.00 UNION ALL --CHANGED FROM 100

select 1, '2/1/2010', 1, 200.00 UNION ALL --ADDED

select 400, '6/15/2009', 451, 1.00 UNION ALL

select 400, '6/30/2009', 451, 1.00 UNION ALL

select 400, '7/15/2009', 451, 1.00 UNION ALL

select 400, '7/30/2009', 451, 1.00 UNION ALL

select 400, '8/15/2009', 451, 5.00

--Before picture--

--select * from #pledge

--select * from #payment

update P

set P.cumlativeamount = (select sum(P2.amount) from #payment P2 where --find cumlative sum of payments including the current one for the given acctid, fundid

P.acctid = p2.acctid

and p.fundid = p2.fundid

and P2.paymentid <= P.paymentid

and P2.date <= P.date

)

from #payment P

--SELECT * FROM #Pledge ORDER BY AcctId, FundID, Date

--SELECT * FROM #Payment ORDER BY AcctId, FundID, Date, PaymentID

update P

set P.cumlativeamount = (select sum(P2.pledged) from #pledge P2 where --find cumlative sum of pledges including the current one for the given acctid, fundid

P.acctid = p2.acctid

and p.fundid = p2.fundid

and P2.id <= P.id

and P2.date <= P.date

)

from #pledge P

--SELECT * FROM #Pledge ORDER BY AcctId, FundID, Date

--SELECT * FROM #Payment ORDER BY AcctId, FundID, Date, PaymentID

--setting pledgeid--

--if cumlative amount of payments (all the payments so far) is < the cumlative amount of 1st pledge

--then that payment will get this pledge id, up till the point they are equal

--then the (next payment+all the payments for the last pledge) will have a cumlative amount > the 1st pledge, then it is time to move on to the next pledge

update P

set P.pledgeid = (select top 1 PP.id from #pledge PP where P.cumlativeamount <= PP.cumlativeamount and PP.fundid=P.fundid and P.Acctid=pp.acctid)

from #payment P

--

--SELECT * FROM #Pledge ORDER BY AcctId, FundID, Date

--SELECT * FROM #Payment ORDER BY AcctId, FundID, Date, PaymentID

update PP

set PP.Received = (select sum(Amount) from #Payment P where PP.fundid=P.fundid and P.Acctid=pp.acctid and PP.ID = P.pledgeid)

from #Pledge PP

SELECT * FROM #Pledge ORDER BY AcctId, FundID, Date

SELECT * FROM #Payment ORDER BY AcctId, FundID, Date, PaymentID

Also the way the table is laid out, there is no way that a payment can be split across two pledges if someone doesn't just happen to make payments that precisely fill each pledge. For example...

DROP TABLE #Pledge,#Payment

GO

CREATE table #Pledge (acctID int, [date] datetime, fundid int, Pledged numeric(8,2),Received numeric(8,2) DEFAULT (0), id int, cumlativeamount numeric(8,2))

insert into #Pledge (acctID, [date], fundid, Pledged, Received, id )

select 1, '2/1/2010', 1, 100.00, 0,500 UNION ALL

select 5, '10/20/1999', 30, 500.00,0, 501 UNION ALL

select 55, '1/15/1997', 501, 500.00, 0,502 UNION ALL

select 400, '5/18/2009', 451, 10.00,0,503 UNION ALL

select 1, '2/1/2010', 1, 100.00, 0,503

CREATE table #Payment(paymentid int identity (1,1),acctID int, [date] datetime, fundid int, amount numeric(8,2), pledgeID int, cumlativeamount numeric(8,2))

insert into #Payment (acctid, [date], fundid, amount)

select 1, '2/1/2010', 1, 75.00 UNION ALL

select 400, '5/30/2009', 451, 1.00 UNION ALL

select 5, '10/20/1999', 30, 300.00 UNION ALL

select 1, '2/1/2010', 1, 75.00 UNION ALL --CHANGED FROM 25

select 1, '2/1/2010', 1, 50.00 UNION ALL --CHANGED FROM 100

select 400, '6/15/2009', 451, 1.00 UNION ALL

select 400, '6/30/2009', 451, 1.00 UNION ALL

select 400, '7/15/2009', 451, 1.00 UNION ALL

select 400, '7/30/2009', 451, 1.00 UNION ALL

select 400, '8/15/2009', 451, 5.00

--Before picture--

--select * from #pledge

--select * from #payment

update P

set P.cumlativeamount = (select sum(P2.amount) from #payment P2 where --find cumlative sum of payments including the current one for the given acctid, fundid

P.acctid = p2.acctid

and p.fundid = p2.fundid

and P2.paymentid <= P.paymentid

and P2.date <= P.date

)

from #payment P

--SELECT * FROM #Pledge ORDER BY AcctId, FundID, Date

--SELECT * FROM #Payment ORDER BY AcctId, FundID, Date, PaymentID

update P

set P.cumlativeamount = (select sum(P2.pledged) from #pledge P2 where --find cumlative sum of pledges including the current one for the given acctid, fundid

P.acctid = p2.acctid

and p.fundid = p2.fundid

and P2.id <= P.id

and P2.date <= P.date

)

from #pledge P

--SELECT * FROM #Pledge ORDER BY AcctId, FundID, Date

--SELECT * FROM #Payment ORDER BY AcctId, FundID, Date, PaymentID

--setting pledgeid--

--if cumlative amount of payments (all the payments so far) is < the cumlative amount of 1st pledge

--then that payment will get this pledge id, up till the point they are equal

--then the (next payment+all the payments for the last pledge) will have a cumlative amount > the 1st pledge, then it is time to move on to the next pledge

update P

set P.pledgeid = (select top 1 PP.id from #pledge PP where P.cumlativeamount <= PP.cumlativeamount and PP.fundid=P.fundid and P.Acctid=pp.acctid)

from #payment P

--

--SELECT * FROM #Pledge ORDER BY AcctId, FundID, Date

--SELECT * FROM #Payment ORDER BY AcctId, FundID, Date, PaymentID

update PP

set PP.Received = (select sum(Amount) from #Payment P where PP.fundid=P.fundid and P.Acctid=pp.acctid and PP.ID = P.pledgeid)

from #Pledge PP

SELECT * FROM #Pledge ORDER BY AcctId, FundID, Date

SELECT * FROM #Payment ORDER BY AcctId, FundID, Date, PaymentID

I guess I'd run this more like a checkbook with the data that you have in two tables combined into one. That way, all you'd need to do is calculate a "simple" running balance. Otherwise, you'd need to run this as if pennies were part of a FIFO inventory system to actually do it correctly where the earliest pledges we paid off first. An example of why that's a problem with the current requirements is show as follows when sums of payments don't precisely match pledge amounts...

DROP TABLE #Pledge,#Payment

GO

CREATE table #Pledge (acctID int, [date] datetime, fundid int, Pledged numeric(8,2),Received numeric(8,2) DEFAULT (0), id int, cumlativeamount numeric(8,2))

insert into #Pledge (acctID, [date], fundid, Pledged, Received, id )

select 1, '2/1/2010', 1, 100.00, 0,500 UNION ALL

select 5, '10/20/1999', 30, 500.00,0, 501 UNION ALL

select 55, '1/15/1997', 501, 500.00, 0,502 UNION ALL

select 400, '5/18/2009', 451, 10.00,0,503 UNION ALL

select 1, '2/1/2010', 1, 100.00, 0,503

CREATE table #Payment(paymentid int identity (1,1),acctID int, [date] datetime, fundid int, amount numeric(8,2), pledgeID int, cumlativeamount numeric(8,2))

insert into #Payment (acctid, [date], fundid, amount)

select 1, '2/1/2010', 1, 75.00 UNION ALL

select 400, '5/30/2009', 451, 1.00 UNION ALL

select 5, '10/20/1999', 30, 300.00 UNION ALL

select 1, '2/1/2010', 1, 20.00 UNION ALL --CHANGED FROM 25

select 1, '2/1/2010', 1, 90.00 UNION ALL --CHANGED FROM 100

select 400, '6/15/2009', 451, 1.00 UNION ALL

select 400, '6/30/2009', 451, 1.00 UNION ALL

select 400, '7/15/2009', 451, 1.00 UNION ALL

select 400, '7/30/2009', 451, 1.00 UNION ALL

select 400, '8/15/2009', 451, 5.00

--Before picture--

--select * from #pledge

--select * from #payment

update P

set P.cumlativeamount = (select sum(P2.amount) from #payment P2 where --find cumlative sum of payments including the current one for the given acctid, fundid

P.acctid = p2.acctid

and p.fundid = p2.fundid

and P2.paymentid <= P.paymentid

and P2.date <= P.date

)

from #payment P

--SELECT * FROM #Pledge ORDER BY AcctId, FundID, Date

--SELECT * FROM #Payment ORDER BY AcctId, FundID, Date, PaymentID

update P

set P.cumlativeamount = (select sum(P2.pledged) from #pledge P2 where --find cumlative sum of pledges including the current one for the given acctid, fundid

P.acctid = p2.acctid

and p.fundid = p2.fundid

and P2.id <= P.id

and P2.date <= P.date

)

from #pledge P

--SELECT * FROM #Pledge ORDER BY AcctId, FundID, Date

--SELECT * FROM #Payment ORDER BY AcctId, FundID, Date, PaymentID

--setting pledgeid--

--if cumlative amount of payments (all the payments so far) is < the cumlative amount of 1st pledge

--then that payment will get this pledge id, up till the point they are equal

--then the (next payment+all the payments for the last pledge) will have a cumlative amount > the 1st pledge, then it is time to move on to the next pledge

update P

set P.pledgeid = (select top 1 PP.id from #pledge PP where P.cumlativeamount <= PP.cumlativeamount and PP.fundid=P.fundid and P.Acctid=pp.acctid)

from #payment P

--

--SELECT * FROM #Pledge ORDER BY AcctId, FundID, Date

--SELECT * FROM #Payment ORDER BY AcctId, FundID, Date, PaymentID

update PP

set PP.Received = (select sum(Amount) from #Payment P where PP.fundid=P.fundid and P.Acctid=pp.acctid and PP.ID = P.pledgeid)

from #Pledge PP

SELECT * FROM #Pledge ORDER BY AcctId, FundID, Date

SELECT * FROM #Payment ORDER BY AcctId, FundID, Date, PaymentID

Like I said... I understand your requirements but I'm not sure that they're truly representative of what could actually happen since humans are making the pledges and the payments.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dbaltazar SSC Eights! Points: 884 More actions · Answer 13

WayneS (2/25/2010)
ray-SQL (2/25/2010)
Try this, not sure it handles all of your scenarios, so more sample data would be good. But it is working for what you given us.
I've modified what you provided. This does not require the three extra columns in the table, and ends up performing just 2 updates instead of 4. (The three columns are derived in CTEs.) What do you think of this?
declare @Pledge table (acctID int, [date] datetime, fundid int, Pledged numeric(8,2),Received numeric(8,2) DEFAULT (0), id int)
insert into @Pledge (acctID, [date], fundid, Pledged, Received, id )
select 1, '2/1/2010', 1, 100.00, 0,500 UNION ALL
select 5, '10/20/1999', 30, 500.00,0, 501 UNION ALL
select 55, '1/15/1997', 501, 500.00, 0,502 UNION ALL
select 400, '5/18/2009', 451, 10.00,0,503 UNION ALL
select 1, '2/1/2010', 1, 100.00, 0,504 -- <<<< NOTE! Changed this to the next number
declare @Payment table (acctID int, [date] datetime, fundid int, amount numeric(8,2), pledgeID int)
insert into @Payment (acctid, [date], fundid, amount)
select 1, '2/1/2010', 1, 75.00 UNION ALL
select 400, '5/30/2009', 451, 1.00 UNION ALL
select 5, '10/20/1999', 30, 300.00 UNION ALL
select 1, '2/1/2010', 1, 25.00 UNION ALL
select 1, '2/1/2010', 1, 100.00 UNION ALL
select 400, '6/15/2009', 451, 1.00 UNION ALL
select 400, '6/30/2009', 451, 1.00 UNION ALL
select 400, '7/15/2009', 451, 1.00 UNION ALL
select 400, '7/30/2009', 451, 1.00 UNION ALL
select 400, '8/15/2009', 451, 5.00
--Before picture--
select * from @pledge
select * from @payment
--setting pledgeid--
--if cumlative amount of payments (all the payments so far) is < the cumlative amount of 1st pledge
--then that payment will get this pledge id, up till the point they are equal
--then the (next payment+all the payments for the last pledge) will have a cumlative amount > the 1st pledge, then it is time to move on to the next pledge
;WITH Payment AS
(
select acctID, date, fundID, amount, pledgeID,
-- simulate an identity column with the row_number function
paymentid = ROW_NUMBER() OVER (ORDER by pledgeID)
from @Payment
), Payment2 AS
(
select acctID, date, fundID, amount, pledgeID,
cumulative = (select sum(P2.Amount)
from Payment P2 --find cumlative sum of payments including the current one for the given acctid, fundid
where P.acctid = p2.acctid
and p.fundid = p2.fundid
and P2.paymentid <= P.paymentid
and P2.date <= P.date)
from Payment P
), Pledge AS
(
select acctID, date, fundID, Pledged, Received, id,
cumulative = (select sum(p2.pledged)
from @pledge P2 --find cumlative sum of pledges including the current one for the given acctid, fundid
where P.acctid = p2.acctid
and p.fundid = p2.fundid
and P2.id <= P.id
and P2.date <= P.date)
from @pledge P
)
update P
set P.pledgeid = (select top 1 PP.id
from Pledge PP
where P.cumulative <= PP.cumulative
and PP.fundid=P.fundid
and P.Acctid=pp.acctid)
from Payment2 P
update PP
set PP.Received = (select sum(Amount)
from @Payment P
where PP.fundid=P.fundid
and P.Acctid=pp.acctid
and PP.ID = P.pledgeid)
from @Pledge PP
--after picture--
select * from @pledge
select * from @payment

I'm finally back in the office and able to look at this again. This is awesome. Just ran it and works perfectly given my example data. I'll look it over more because there's alot here I'm not familiar with syntax wise. Then I'll try to apply the method to some real client data. Thanks for your help.

dbaltazar SSC Eights! Points: 884 More actions · Answer 14

I have been looking at this and looking at it and it works great for my test data that I provided here but in some client data I have it's overpaying the pledge.

I think I'm close to figuring out why though. Can someone explain to me what the purpose of this line is and how it works? I can't seem to figure it out:

-- simulate an identity column with the row_number function

paymentid = ROW_NUMBER() OVER (ORDER by pledgeID)

dbaltazar SSC Eights! Points: 884 More actions · Answer 15

Here is a good example of where this syntax is overpaying. I think it has something to do with the over (row number) line but I'm not 100% sure. In this scenario I end up with the $10 pledge received $7 and the $13 pledge receiving $16. One is overpaid while the other is underpaid. That $3 payment ends up going to the wrong pledge.

declare @Pledge table (acctID int, [date] datetime, fundid int, Pledged numeric(8,2),Received numeric(8,2) DEFAULT (0), id int)

insert into @Pledge (acctID, [date], fundid, Pledged, Received, id )

select 5288, '5/28/1993', 1000068, 10.00, 0,1016375 UNION ALL

select 5288, '5/28/1993', 1000068, 13.00,0, 1016381

declare @Payment table (id int, acctID int, [date] datetime, fundid int, amount numeric(8,2), pledgeID int)

insert into @Payment (id, acctid, [date], fundid, amount)

select 1016376, 5288,'7/2/1993', 1000068, 1.00 UNION ALL

select 1016377, 5288,'6/24/1993', 1000068, 1.00 UNION ALL

select 1016378, 5288,'7/12/1993', 1000068, 1.00 UNION ALL

select 1016379, 5288,'7/28/1993', 1000068, 1.00 UNION ALL

select 1016380, 5288,'9/7/1993', 1000068, 1.00 UNION ALL

select 1016382, 5288,'8/5/1993', 1000068, 1.00 UNION ALL

select 1016383, 5288,'11/28/1993', 1000068, 6.00 UNION ALL

select 1016384, 5288,'5/26/1994', 1000068, 3.00 UNION ALL

select 1016385, 5288,'8/20/1993', 1000068, 1.00 UNION ALL

select 1016386, 5288,'3/10/1994', 1000068, 7.00

--Before picture--

select * from @pledge

select * from @payment

--setting pledgeid--

--if cumlative amount of payments (all the payments so far) is < the cumlative amount of 1st pledge

--then that payment will get this pledge id, up till the point they are equal

--then the (next payment+all the payments for the last pledge) will have a cumlative amount > the 1st pledge, then it is time to move on to the next pledge

;WITH Payment AS

(

select acctID, date, fundID, amount, pledgeID,

-- simulate an identity column with the row_number function

paymentid = ROW_NUMBER() OVER (ORDER by pledgeID)

from @Payment

), Payment2 AS

(

select acctID, date, fundID, amount, pledgeID,

cumulative = (select sum(P2.Amount)

from Payment P2 --find cumlative sum of payments including the current one for the given acctid, fundid

where P.acctid = p2.acctid

and p.fundid = p2.fundid

and P2.paymentid <= P.paymentid

and P2.date <= P.date)

from Payment P

), Pledge AS

(

select acctID, date, fundID, Pledged, Received, id,

cumulative = (select sum(p2.pledged)

from @pledge P2 --find cumlative sum of pledges including the current one for the given acctid, fundid

where P.acctid = p2.acctid

and p.fundid = p2.fundid

and P2.id <= P.id

and P2.date <= P.date)

from @pledge P

)

update P

set P.pledgeid = (select top 1 PP.id

from Pledge PP

where P.cumulative <= PP.cumulative

and PP.fundid=P.fundid

and P.Acctid=pp.acctid)

from Payment2 P

update PP

set PP.Received = (select sum(Amount)

from @Payment P

where PP.fundid=P.fundid

and P.Acctid=pp.acctid

and PP.ID = P.pledgeid)

from @Pledge PP

--after picture--

select * from @pledge

select * from @payment