How do I form an SQL query using DATE_SUB to check for yesterday's data OR if it's Monday, to check for Friday's data?

Question

Here's my query:

SELECT *
FROM daily_records 
AND date = DATE_SUB(CURDATE(), INTERVAL 1 DAY)

I use this to generate a report of everything that happened yesterday. This works great, Tuesday-Friday. However, on Mondays, I want it to be able to search back to Friday (the previous business day, eg INTERVAL 3 DAY).

Is there a way to do this in mySQL? Or do I just need to check the day of the week in PHP before writing the query?

Answer 1

You could do...

SELECT *
FROM daily_records
WHERE date = IF(DAYOFWEEK(CURDATE()) = 2, DATE_SUB(CURDATE(), INTERVAL 3 DAY), DATE_SUB(CURDATE(), INTERVAL 1 DAY))

Answer 2

This should do it:

SELECT * FROM daily_records AND date =
DATE_SUB(CURDATE(), INTERVAL IF(DATE_FORMAT(NOW(), '%w') = 1, 3, 1) DAY)

Answer 3

mysql> create function PREV_BIZ_DATE ()
    -> returns DATE
    -> return (CURRENT_DATE() - interval if(DAYOFWEEK(CURRENT_DATE()) = 2, 3, 1) day);

mysql> SELECT * FROM daily_records WHERE date = PREV_BIZ_DATE();