I have a complicated system that maybe you can help me figure out how to build a SQL statement to get this in "one shot."

I am building a system that tells the user whether or not their license is expired. This license is a little complex as it varies for different people with different criteria, so I needed to build a user interface to allow the person to set the criteria. That's all fine. Some of the important criteria are:

Number of events required
Time frame required

I need to use SQL to go back and see when THE LATEST date is that the person met this requirement THEN see if this is within the requirement window to ensure they are not expired. Let me further explain with an example.

A person flies an airplane with the following entries in their log:

Date Flights
7/21 1
7/30 2
8/6 2
8/24 1
9/1 1

The user configured there currency checker to say that they need 3 Flights in the last 30 days.

What needs to happen next is use SQL to determine exactly when they most recently accomplished 3 flights. So, I need to go back and step through in DATE DESCENDING order, keeping a running sum of the flights, until the flights are >= 3.

In the example above, I would come up with 8/6 as the date. THEN I need to add 30 days to this to determine the Expiration date.

So, to summarize, I need to create ONE SQL statement that determines the LAST date in which the user accomplished X number of flights with the XX time frame (X and XX are variables the user configures, assuming days in this example).

Now that the number date that satisfies the minimum required (latest date,i.e. Date DESC), I need to add the time period required to come up with the "forecasted" expiration date.

I know I can create a Recordset then iterate the recordset with a running sum and then stop at the time the sum is >= to the requirement, then add the time period to that date, but I'm wondering if I can actually build a SQL statement that does all this and just returns the expiration date?

Up for the challenge? If it's even a challenge!

Thanks for any help, SQL is great, I know this can be done, I am just at a loss for doing this the most efficient and fastest method.

Sincerely,

Neal Culiner
NC Software, Inc.
www.nc-software.com