March 5, 2010 at 9:25 am
I was able to fix that scenario by changing the over order by line to amount not ID. That fixes one record but still leaves another record broken.
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 UNION ALL
select 1211, '9/13/2001', 1000043, 312.00,0, 1004386 union all
select 1211, '9/13/2001', 1000043, 312.00,0, 1004396
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 UNION ALL
select 1004380, 1211, '9/29/2003' , 1000043, 24.00 UNION ALL
select 1004381, 1211, '6/1/2004' , 1000043, 20.00 UNION ALL
select 1004382, 1211, '11/28/2003' , 1000043, 28.00 UNION ALL
select 1004383, 1211, '12/31/2002' , 1000043, 104.00 UNION ALL
select 1004384, 1211, '6/2/2003' , 1000043, 44.00 UNION ALL
select 1004385, 1211, '12/31/2003' , 1000043, 8.00 UNION ALL
select 1004387, 1211, '3/31/2004' , 1000043, 24.00 UNION ALL
select 1004388, 1211, '12/26/2001' , 1000043, 28.00 UNION ALL
select 1004389, 1211, '11/28/2003' , 1000043, 28.00 UNION ALL
select 1004390, 1211, '12/31/2002' , 1000043, 104.00 UNION ALL
select 1004391, 1211, '11/26/2004' , 1000043, 8.00 UNION ALL
select 1004392, 1211, '8/31/2004' , 1000043, 24.00 UNION ALL
select 1004393, 1211, '8/31/2004' , 1000043, 24.00 UNION ALL
select 1004394, 1211, '3/31/2004' , 1000043, 24.00 UNION ALL
select 1004395, 1211, '11/26/2004' , 1000043, 8.00 UNION ALL
select 1004397, 1211, '9/29/2003' , 1000043, 24.00 UNION ALL
select 1004398, 1211, '6/2/2003' , 1000043, 44.00 UNION ALL
select 1004399, 1211, '12/26/2001' , 1000043, 28.00 UNION ALL
select 1004400, 1211, '6/1/2004' , 1000043, 20.00 UNION ALL
select 1004401, 1211, '12/31/2003' , 1000043, 8.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 amount) --changed to amount
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
March 5, 2010 at 2:22 pm
Well, they say only death and taxes are certain, I think we need to add "bad table design will cause problems!" Jeff pointed out earlier that "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..."
And that is what is happening...
There were two issues going on:
1) I didn't test Wayne's code throughly before but now I see the order by for Payment is on PledgeID, because at that point PledgeID is all null so the ordering is essentially random
drop table #pledge
drop table #Payment
Create table #Pledge (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
create table #Payment (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
select acctID, date, fundID, amount, pledgeID,
-- simulate an identity column with the row_number function
paymentid = ROW_NUMBER() OVER (ORDER by pledgeID)
from #Payment
I suggest we change thisselect acctID, date, fundID, amount, pledgeID,
-- simulate an identity column with the row_number function
paymentid = ROW_NUMBER() OVER (ORDER by pledgeID)
from #Payment
to
select acctID, date, fundID, amount, pledgeID,
-- simulate an identity column with the row_number function
paymentid = ROW_NUMBER() OVER (ORDER by Date, amount)
from #Payment
This ordering seems to make sense to me, earlier payment go with earlier pledges. If you are unfamilar with ROW_NUMBER, please look it up in the BOL.
2) BUT, even with this going on, we are still faced how to divide up the $6 across two pledges. Even if we manage to do, still presenting you with the problem of which pledge id to update $6 with, should it be 1016375 or 1016381? Each of these received $3 from this $6.
Because you say you can't change the data structure perhaps your only option left is what Jeff suggested, keeping a running total and call it a day.
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply