Mysql statement where not exists with time intervals

I am basically trying to get my app to report when items are not returned or overdue in a 5 day and 15 day window. After much trial and error the below coded is the best I could come up with. However, when I extend to 15 days I still receive items that were returned (hence not exists not working appropriately) based off of movieid. The goal is see items from checkout that have not been returned with a time frames to notify members of late dates. Any suggestions to improve this statement?

select movieid, dueback
from checkout as a
where NOT exists 
(
    select * from returns as b
    where a.movieid = b.movieid
    AND dueback < DATE_SUB(NOW(), INTERVAL 5 DAY)
)
ORDER by dueback;

CHECKOUT TABLE: checkoutid, outdate, dueback, movieid, customerid, payment

RETURNS TABLE: returnid, today, movieid

Answer

You can use BETWEEN using the two computed dates. See Between documentation, but you can do something like:

 AND dueback BETWEEN DATE_SUB(NOW(), INTERVAL 5 DAY) AND DATE_SUB(NOW(), INTERVAL 15 DAY)

That way you will get all dueback that is between 5 and 15 days from it’s date.

Advertisement

Answer