Query to Calculate Due Date - Where do they come up with These?

I am creating a query that is the source of a DTS transformation to load an Excel spreadsheet. It is account data that has rejected from our billing system. There is an Account, Reject Date, and other data. The first requirement was that I calculate a Due Date that is [RejectDate] + 7 days. No problem.

The specs then changed to say that the due date is the oldest reject date within 7-days of the most recent reject date + 7 days.

Example (in the date order in which data is received):
ID      Acct       RejectDate    Calculated Due Date
01     Acct1     10-18-2007    10-25-2007
02     Acct1     10-20-2007    10-25-2007 (because 10-18 is within 7 days of 10-20, so use 10-18 + 7)

based on this example, if I had
03    Acct1     10-26-2007, you would expect a due date of 10-27-2007 (10-20 is within 7-days, so use that date + 7 days)

However, the specs have changed now. If the immediately previous record (ID=02), is within a 7-day window of a record prior to it (which is the case, with ID=01), and the new record (ID=03) is outside the window of the first record (ID=01), then start over, so ID=03's Due Date should be 10-26 (its reject date) + 7-days.

The logic is that we have a seven-day window to work rejects. Any new rejects for the same account that come in during that window can be considered as being worked within that same window. However, new that comes in outside the 'original' 7-day window is given another 7-days to be worked.

For a clearer example (and maybe to add even more confusion) the following sequence might help make it clearer (AcctID is just an identity column) and this represents 10-days of rejects fro the same account:

[AcctID]   [Account]   [RejectDate]   [Calculated Due Date]
1             Acct001      10-01-2007    10-07-2007
2             Acct001      10-02-2007    10-07-2007
3             Acct001      10-03-2007    10-07-2007
4             Acct001      10-04-2007    10-07-2007
5             Acct001      10-05-2007    10-07-2007
6             Acct001      10-06-2007    10-07-2007
7             Acct001      10-07-2007    10-07-2007
8             Acct001      10-08-2007    10-14-2007
9             Acct001      10-09-2007    10-14-2007
10           Acct001      10-10-2007    10-14-2007

FYI, DUE DATE is calculated at the time the query is run each day and I have not had any problem with either of the first two request, but this latest one is giving me headaches. If it would GREATLY facilitate the query to add a Due Date column to the table, I would be willing to do that as a last resort.Start Free Trial