Dynamic expiration date

[oakes6b2]

OK...here is my deal:

 

I have a recordset that shows the user's sum of instrument approaches and hold (flying) from the last 6 months.  I have written the code to show that if the approaches >= 6 and holds >= 1 then it tells the user that they are instrument current.  Here is where I have a problem.  Once the date that the users approaches goes from 6 to something less than 6, OR the holds goes from 1 to zero, I want to show them a date 6 months from that date to tell them when when their instrument currency will expire.  Does this make sense?  Can I do this somehow?

[obsidian]

I'm not sure I understand the relationship with all the holds and approaches, but if you can narrow down the initial date you are wanting to calculate from yourself, you can easily let MySQL do the rest for you:

SELECT custom_date + INTERVAL 6 MONTH AS exp_date;

[artacus]

I'm not quite sure how all of your logic works. But I think I would probably have a separate table to store that info. Probably something like req_met_date, req_exp_date and currency_exp_date.

 

So if they meet the req, they'd get an entry in req_met_date. When they drop below the requirement, you'd set the req_exp_dat and currency_exp_date (use DATE_ADD(req_exp_date, INTERVAL 6 MONTH) if that is what you are asking). If they meet the requirements again, you'd clear out the req_exp_date and currency_exp_date fields as long as it hasn't totally expired. If it did, you should add a new entry when the become current again.

[oakes6b2]

OK...maybe this will explain it better.  I have a table that logs all approaches in the last 6 months along with the date.  Now the user may log more than one approach on any given day.  Now what I want to do is identify the date, within the last  months, that when it reaches it's six month expiration will make the sum of my approaches drop below 6.

 

|  Date  |Approaches|

| ------- | ----------|

|  1/1/07 |      3        |  Now when this date falls off the 6 month table, my sum is still 9 = user current

| 1/15/07|      4        |  When this date falls off the 6 month table, my sum is 5 = user not current

| 3/23/07|      5        | 

 

How can I identify in advance of 7/15/07 (1/15/07 + 6 months) that on this date their currency will expire?  Does that make sense?