0

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

Soumendra Mishra
  • 3,483
  • 1
  • 12
  • 38
sopapsql
  • 3
  • 1
  • Does this answer your question? [How do I query between two dates using MySQL?](https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql) – Marco Sep 25 '20 at 18:25

1 Answers1

0

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.

Marco
  • 2,757
  • 1
  • 19
  • 24
  • 1
    Thank you. I was thinking between as well but not so. To make it more clear, So I only want 5 days late (movieid not in returns) so I can email out reminder. Secondly a separate 15 days email that a price to buy is now in place. I thought NOT exist would fix that but I still get dueback dates on movieids that have been returned. I am missing a small or simple part but still trying to solve. Basically (does rerturns tables and checkout table see a shared movieid with a 5 day window or not and a separate statement for 15 day. Thanks for any advice or direction. – sopapsql Sep 25 '20 at 18:39
  • Just use (OR) then. The filter you already were using, OR (condition for the 15 days). – Marco Sep 25 '20 at 18:49
  • 1
    OR!!!!! Versus AND. Thank you Marco. That solved it. Knew it was simple but brain block. Thank you again. I learned much from this. – sopapsql Sep 25 '20 at 19:03