Looking for process Ideas

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.