Select the previous business day by using SQL

Question

What is the purely MySQL way of getting yesterday, but only for business days?

Right now I'm using SUBDATE(CURRENT_DATE, 1)) for yesterday but on Monday it's returning Sunday.

I would like it to return results for the previous Friday's date, or in other words, the previous business day.

I should have clarified I'm trying to use this in the WHERE part of the query (and also the subquery).

SELECT ... WHERE DATE(timestamp) = SUBDATE(CURRENT_DATE, 1))

Here is the whole query:

SELECT r.drivername, l.branchcity as location, COUNT(o.ordernum) as deliveries, COUNT(x.pictures) as pics, CONCAT(ROUND((COUNT(x.pictures) / COUNT(o.ordernum))*100,2),'%') as percentage_having_images 
FROM deliveries d, drivers r, locations l, staging s, stations t, orders o LEFT OUTER JOIN 
(SELECT a.ordernum AS pictures
FROM orders a WHERE a.stationID = '16' AND DATE(a.scantime) = SUBDATE(CURRENT_DATE, 1)) x 
ON x.pictures = o.ordernum
WHERE o.deliveryID = d.ID AND d.driverID = r.ID AND s.locationID = l.ID AND o.stationID = t.ID AND o.stagingID = s.ID AND t.ID IN ('11','12','13') AND DATE(o.scantime) = SUBDATE(CURRENT_DATE, 1) GROUP BY s.locationID, r.drivername ORDER BY s.locationID, percentage_having_images DESC

Answer 1

SELECT ..
.
.
AND DATE(a.scantime) = (CASE WEEKDAY(CURRENT_DATE) 
                             WHEN 0 THEN SUBDATE(CURRENT_DATE,3)
                             WHEN 6 THEN SUBDATE(CURRENT_DATE,2) 
                             WHEN 5 THEN SUBDATE(CURRENT_DATE,1)
                             ELSE SUBDATE(CURRENT_DATE,1) 
                        END)
..
..