I'm looking for Ideas on how to handle a Pldege Reminder process. For example; a pledge is made to pay $2400 over the next two years. They will pay $100 per month and each month a reminder will be sent. No real mistery if there is a balance you send a reminder. My problem is how to handle things like what if they want to pay quarterly or annually and how to determine if a payment is really due based on when they paid last, etc... You most likely see what I mean.
If anyone has done this in the past and/or has any ideas and is willing to share I would greatly appreciate any help.
Some stuff that may help you help me better:
tblClient (ClientID)
tblPledge (PledgeID, ClientID, PledegedAmt, PledgeDate,Frequency,NumberYears)
tblPledgePayments (PmtID, PledgeID,PmtAmt,PmtDate)
Using the following definition:
Client (Id, Name, Email)
Pledge (Id, ClientId, PledgedAmt, PledgeDate, Frequency, NumberOfPeriods)
PledgePayments (Id, PledgeId,PmtAmt,PmtDate)
Try
SELECT Name, Email FROM Client WHERE Id IN (
SELECT ClientId FROM Pledge, PledgePayments WHERE Frequency='M' AND PledgePayments.PledgeId = Pledge.Id
AND (SUM(PmtAmt) < (PledgedAmt * (DateDiff(m, GetDate(), PledgeDate) / NumberOfPeriods)))
You will need to look up DateDiff in Books-on-line and check that m is the indicator for months. Also that GetDate() and PledgeDate are the right way around.
HTH
|||Have you done this sort of thing in the past with pledges?|||No! It is however a fairly simple bit of SQL. You will need to test the query with a number of test cases.
