CODEWITHSUNDEEP
mysqlsqldate
jjclarkson
jjclarkson

Reputation: 5954

Select the previous business day by using SQL

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

Upvotes: 4

Views: 10163

Answers (3)

flaschenpost
flaschenpost

Reputation: 2235

OK, so not the easiest to read solution, but a bit shorter and cooler. ;-)

SELECT CURRENT-DATE - if((6+weekday(@dat))%7 > 4, (6+weekday(@dat))%7-3, 1) as  prev_workday

Explain: At least in my timezone gives weekday('monday-date') = 0, at Sunday 6. With +6 I shift that to 6 .. 12. With modulu 7 (%7) I shift that to Saturday=4, Sunday=5, Monday=6.

So on all days which are after that shift bigger than 4 I reduce the day for this value - 3, otherwise I take the 'day before'. So on nsaturday I reduce for one day, on Sunday for two days and on Monday for three days.

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

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)
..
..

Upvotes: 11

esdebon
esdebon

Reputation: 2729

try this to get the day:

     SELECT
CASE DAYOFWEEK(SUBDATE(CURRENT_DATE, 1))
      WHEN 1 THEN SELECT SUBDATE(CURRENT_DATE, 3);
      WHEN 7 THEN SELECT SUBDATE(CURRENT_DATE, 2);
      ELSE
        BEGIN
          SUBDATE(CURRENT_DATE, 1);
        END;
    END CASE;

THEN use a subquery

SELECT ... WHERE DATE(timestamp) = (SELECT
    CASE DAYOFWEEK(SUBDATE(CURRENT_DATE, 1))
          WHEN 1 THEN SELECT SUBDATE(CURRENT_DATE, 3);
          WHEN 7 THEN SELECT SUBDATE(CURRENT_DATE, 2);
          ELSE
            BEGIN
              SUBDATE(CURRENT_DATE, 1);
            END;
        END CASE);

Upvotes: 0

Related Questions