I have a database that tracks classified ads in a weekly publication. Each customer has an account and there can be an unlimited number of ads per account. (One-to-many) My question relates to the expiration dates of these ads and the subsequent invoice procedure. Here's why I'm stumped:

We are setup to bill a customer at: 1) Expiration of the ad or 2) The end of the billing cycle - whichever comes sooner. So in other words, if the customer is scheduled to run ad until December 1, but they call in and cancel the ad on November 28 (creating an expiration), the invoice would be generated immediately on the same day. If the same customer was scheduled to run an ad until December 10 and the current billing cycle ends on December 2, they would be billed on December 2nd and the remainder of the ad schedule (8 days in this case) would carry over onto the next invoice. My question is, how can I create a rolling expiration that will be able to handle this invoice situation?

Th only idea I had come up with was to only allow each classified ad to run for a period of 4 weeks. At the end of that 4 weeks, if the ad hasn't been cancelled yet, it would be automatically duplicated and added to the customers account. So for instance, if the ad was number 101 and was setup to run for 4 weeks the situation would look like this:

Ad 101 runs from 11/4/04 until 11/25/04 (4 weeks), but the billing cycle ends on 11/18/04. The database would automatically create an invoice for the customer that contains everything from the start date of the ad up until the billing date - or 11/4/04 through 11/18/04 (3 weeks) and the remaining 1 week would stay in the system until 11/25/04 at which time it was scheduled to expire. The customer would get an invoice for the first 3 weeks and then a separate invoice for the last week.

If anyone has any suggestions on how I can code this up, I would greatly appreciate hearing them. Thanks in advance and have a good weekend all.