February 25, 2010 at 9:13 am
Greetings,
I need some help figuring out how to do this in SQL Server. I have a scenario where I have 2 tables. Pledge and Payment. Pledge is basically a promise table where someone promises to give an amount. Payment is where they actually pay on that promise. They can make more than 1 payment to a pledge.
In my particular scenario my data looks like this:
Pledge table:
______
acctID date fundid amount id received
1 2/1/2010 1 100.00 500
1 2/1/2010 1 100.00 501
Payment table:
______________
acctid date fundid amount pledgeID
1 2/1/2010 1 75.00
1 2/1/2010 1 25.00
1 2/1/2010 1 100.00
What I need to happen is for the pledge.received to be set to 75.00 on the first pass. It finds another payment for 25.00 so pledge received
should = 100.00 now (75+25). At that same time its updating received it's marking payment.pledgeid with the pledge.id so we know that payment has been used. Once those 2 payments totalling $100 are found it should stop applying payments to that pledge and move to the next one.
I know how to do this in Foxpro which is what I'm used to using. SQL Server is still kind of new to me and I don't know how to get it to work in SQL. I've been trying loops, transactions but everytime it ends up that $200 gets applied to the first pledge (thus overpaying it) and nothing to the 2nd pledge.
Thanks in advance for any help.
Here is my transaction method code:
UPDATE testpymt set testpymt.d2pledgeid=testpldg.d2id2 from testpymt inner join testpldg on
testpymt.acctno=testpldg.acctno and testpldg.d2fundid=testpymt.d2fundid
and testpldg.origpldgdate=testpymt.origpldgdate
where testpymt.date>=testpldg.date and testpymt.d2pledgeid is null and
testpldg.received+testpymt.d2giftamt<=testpldg.Pledged
-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error in updating Pledges.', 16, 1)
RETURN
END
UPDATE testPldg set testpldg.received=testpldg.received+testpymt.d2giftamt
from testpldg inner join testpymt on
testpldg.d2id2=testpymt.d2pledgeid
-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error in updating Payments.', 16, 1)
RETURN
END
-- STEP 4: If we reach this point, the commands completed successfully
-- Commit the transaction....
COMMIT
select * from testpldg where origpldgdate is null order by acctno
select * from testpymt where origpldgdate is null order by acctno
--Those without origpldgdate
BEGIN TRANSACTION
-- STEP 2 & 3: Update Pledge checking @@ERROR after each statement
UPDATE testPldg set testpldg.received=testpldg.received+testpymt.d2giftamt
from testpldg inner join testpymt on
testpldg.d2id2=testpymt.d2pledgeid where testpldg.origpldgdate is null and testpymt.origpldgdate is null
-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error in updating Pledges.', 16, 1)
RETURN
END
UPDATE testpymt set testpymt.d2pledgeid=testpldg.d2id2 from testpymt inner join testpldg on
testpymt.acctno=testpldg.acctno and testpldg.d2fundid=testpymt.d2fundid
where testpymt.date>=testpldg.date and testpymt.d2pledgeid is null and
testpldg.received+testpymt.d2giftamt<=testpldg.Pledged and
testpldg.origpldgdate is null and testpymt.origpldgdate is null
-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error in updating Payments.', 16, 1)
RETURN
END
-- STEP 4: If we reach this point, the commands completed successfully
-- Commit the transaction....
COMMIT
February 25, 2010 at 10:03 am
I have 2 ideas.
1) If you MUST use this schema, then I would probably write a stored procedure that asked for the accountid and pledgeID, and perform an insert into the payment table and an update on the pledge table (I didn't see any values for pledgeID in the data you posted)
2) If you can alter this schema, why not just drop the field for received in the pledge table and then create a view to tell you what you need to know
create view vPledgeDashboard as
Select a.accountid AccountID, a.amount PledgeAmount, b.SumPayments PledgePayments, a.amount-b.SumPayments PledgeBalance
from
Pledge a
inner join
(select accountID, pledgeID, sum(amount) SumPayments from Payment group by accountID, pledgeID) b
on a.accountID = b.accountID and a.pledgeID = b.pledgeID
In the end, I'd specify the pledgeID in every payment transaction and it might make coding and expansion over time, a bit easier.
One of my reaosns for specifying the pledgeID is because you don't know if the donor perhaps wanted to pay on pledge #2 instead of pledge #1.
February 25, 2010 at 10:13 am
No insert can occur. The tables already exist with data in them. I have no control over that.
February 25, 2010 at 10:19 am
How do you know which payment goes with which pledge?
February 25, 2010 at 10:22 am
In the scenario posted my accountid's match as well as my fundid and dates.
In real life all of the above is true except the payment date may be equal to or larger than the pledge date.
February 25, 2010 at 10:24 am
Sounds like he doesn't. Is it correct that you just apply payments until a pledge is filled and then next payment is toward the next pledge?
Could you have the scenario where you
pledge #1 = 100
pledge #2 = 50
payment #1 = $60
payment #2 = $50
and after you fulfilled pledge #1, you need to apply the $10 left over to pledge #2? If so, this design left something to be desired.
February 25, 2010 at 10:28 am
It's unusual to have money left over. It does happen once in awhile. Where someone decides to pay more than they promised. In that scenario we raise the pledge amount. But it's rare.
You are correct in your understanding how this works though. Match payments to a pledge and once the first pledge is fulfilled (paid in full) we move to the next pledge for that account.
What may happen is a pledge won't have any payments because they haven't made any yet. Or lets say a pledge is made for $50.00 and they only pay $25. That's ok. The sum of payments can be less than or equal to the pledge amount. In the rare scenario that they are greater than the original pledge we raise the pledge amount. But that can be 2 out of thousands if any.
In Foxpro I would do a seek from payment into pledge with a while loop. I suspect a while loop is the answer here as well but how to update both tables within that loop? And than how to keep them from being overpaid?
February 25, 2010 at 10:36 am
This is definitely doable. Just few more questions first,
1) do the payment dates always equal to pledge date? Can I pledge today and pay next week? In other words, do you have a payment.date=pledge.date or is it more like payment.date >= pledge.date?
2) what is the meaning of fundid? is it necessary for matching?
What I am getting at is trying to understand how to join your two tables, is there any other link besides the account id and possibly the payment.date >= pledge.date?
While loop will work but what is your volume? If it is decent size then we will need to think about it.
February 25, 2010 at 10:37 am
First, please provide the table structure information, and test data via "CREATE TABLE" / "DECLARE @TableVar TABLE" and "INSERT" statements (see the first link in my signature for how to do that). This makes it a lot easier for all of us to be on the same page when trying to help you. Then, with the sample data provided, can you show us what the expected results are to be?
I've taken the sample data you gave earlier and tried to put this into this format:
declare @Pledge table (acctID int, [date] datetime, fundid int, amount numeric(8,2), id int, received numeric(8,2) DEFAULT (0))
insert into @Pledge (acctID, [date], fundid, amount, id )
select 1, '2/1/2010', 1, 100.00, 500 UNION ALL
select 1, '2/1/2010', 1, 100.00, 501
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 1, '2/1/2010', 1, 25.00 UNION ALL
select 1, '2/1/2010', 1, 100.00
Based on this sample data, what causes the payment for $75 to be applied to the first pledge, and not the second one? In other words, what data in two tables defines that join condition between the two tables, so that it will only update the proper record?
We need some realistic sample data from you so that we can help you out.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 25, 2010 at 10:38 am
The relationship between the tables is acctid and fundid. The payment.date can be >= pledge.date never less than.
February 25, 2010 at 10:41 am
Yes... and all of your sample data is for 1 account, and 1 fund, on one date. How do we tell that the $75 payment goes to the first pledge, and not the second one?
To help us help you, can you supply us with better sample data?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 25, 2010 at 10:43 am
I think I hear an echo in my head.
February 25, 2010 at 10:47 am
WayneS (2/25/2010)
First, please provide the table structure information, and test data via "CREATE TABLE" / "DECLARE @TableVar TABLE" and "INSERT" statements (see the first link in my signature for how to do that). This makes it a lot easier for all of us to be on the same page when trying to help you. Then, with the sample data provided, can you show us what the expected results are to be?I've taken the sample data you gave earlier and tried to put this into this format:
declare @Pledge table (acctID int, [date] datetime, fundid int, amount numeric(8,2), id int, received numeric(8,2) DEFAULT (0))insert into @Pledge (acctID, [date], fundid, amount, id )
select 1, '2/1/2010', 1, 100.00, 500 UNION ALL
select 1, '2/1/2010', 1, 100.00, 501
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 1, '2/1/2010', 1, 25.00 UNION ALL
select 1, '2/1/2010', 1, 100.00
Based on this sample data, what causes the payment for $75 to be applied to the first pledge, and not the second one? In other words, what data in two tables defines that join condition between the two tables, so that it will only update the proper record?
We need some realistic sample data from you so that we can help you out.
The sample data already given is realistic but I'll work on getting more. AcctID+FundID are the key between the 2 tables. I want to loop thru payment looking into pledge and say ok, here's the first payment I find. The payment date is >= pledge date. The payment.amount<= pledge.amount so mark pledge.received with the payment.amount so we know what they paid and than mark the payment table's pledgeID with the pledge.ID so we know that pledge belongs to that payment.
February 25, 2010 at 10:47 am
dbaltazar, hope you don't mind us asking all these questions, we are trying to understand.
I also agree with what is being said, it seems like this should be an application layer/business fix rather than coming up with loosely linked tables and fix it in SQL.
If payment is done on the website, why can't they do "select pledgeid from pledgetable where received < amont" and display all these open pledges and have the customer pick which one they are giving to? So you will have pledge id in the Payment table and then it is just a simple update to the pledge table.
February 25, 2010 at 10:51 am
I do database conversions where I take historical client data who purchase our software and make it fit our system. Sometimes the client data isn't very rationalized. This was easy to do in Foxpro. Since Foxpro is dying (dead) and our database that we are converting historical data into is SQL Server we are utilizing SQL Server (SSIS sometimes) to do conversions on now. This way I don't have to go from xls to Fox to SQL. I can go straight from xls to SQL.
Edited to add I don't mind the questions. I understand your trying to help. I appreciate it.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply