Hi friends,

I need to fetch the records which are about to expire based on regular intervals such as 90, 60 and 30 days based on the given date, and also need to enter the retrieved records information into one audit table, and then whenever we are fetching the records again on the same day, it should not fetch the records again.

say I have one table named user_info which has the following values.

user_info
---------
user_id, expiry_date
1, 2015-09-19
2, 2015-10-19
3, 2015-11-19


I thought of using the below query to fetch the records based on the interval

SELECT * FROM user_info WHERE NOW() < expiry_date - INTERVAL 30 DAY ORDER BY expiry_date DESC

and if I am getting the data from the above query, I will be inserting them into one audit table say eg: user_info_audit

user_info_audit
----------------
user_id,expiry_date,interval
1, 2015-09-19,30


but how can I restrict the already fetched data using the user_info_audit table?
I wanted to use a single query for retrieving the records based on the given intervals (90,60,30)

can I use the below query? If so, how can I restrict it from fetching it again during re-run using the audit table?

SELECT user_id,expiry_date, 30 interval_time FROM user_info WHERE NOW() < expiry_date - INTERVAL 30 DAY ORDER BY expiry_date DESC
Union
SELECT user_id,expiry_date, 60 interval_time FROM user_info WHERE NOW() < expiry_date - INTERVAL 60 DAY ORDER BY expiry_date DESC
Union
SELECT user_id,expiry_date, 90 interval_time FROM user_info WHERE NOW() < expiry_date - INTERVAL 90 DAY ORDER BY expiry_date DESC


Could anyone please help me on the solution please?