Grabbing Data from Past 3 Business Days

Question

I'm looking to get any data that occurred during the past 3 business days.
If today is a Monday, I want my query to pull for data that happened on Wednesday, Thursday, and Friday of last week.
If it's Tuesday, pull data that occurred on Thursday, Friday, and Monday. If it's Wednesday, pull for Friday, Monday, Tuesday. Etc.

This is what I have so far in my WHERE statement, but I know it is incorrect.

AND CASE
WHEN DATENAME(DW, GETDATE()) = 'Monday' THEN CAST(PE.SchedDt AS DATE) BETWEEN DATEADD(DAY,-5,CAST(GETDATE()AS DATE)) AND DATEADD(DAY,-3,CAST(GETDATE()AS DATE))
WHEN DATENAME(DW, GETDATE()) = 'Tuesday' THEN CAST(PE.SchedDt AS DATE) BETWEEN DATEADD(DAY,-5,CAST(GETDATE()AS DATE)) AND DATEADD(DAY,-1,CAST(GETDATE()AS DATE))
WHEN DATENAME(DW, GETDATE()) = 'Wednesday' THEN CAST(PE.SchedDt AS DATE) BETWEEN DATEADD(DAY,-5,CAST(GETDATE()AS DATE)) AND DATEADD(DAY,-2,CAST(GETDATE()AS DATE))
ELSE CAST(PE.SchedDt AS DATE) BETWEEN DATEADD(DAY,-3,CAST(GETDATE()AS DATE)) AND DATEADD(DAY,-1,CAST(GETDATE()AS DATE))
END

Any help with this is much appreciated!

Volokh Volokh · Accepted Answer · 2018-02-16T15:09:11

You can assign the weekday to each date with the WEEKDAY() function. Then you have to compare whether your current weekday WEEKDAY(CURDATE()) is within the range of 3.

        CREATE TABLE data_table (date_column DATE, some_data VARCHAR(1));

        INSERT INTO data_table 
        VALUES ('2018-02-12', 'b'),('2018-02-14', 'a'),('2018-02-13', 'c'),
    ('2018-02-15', 'b'),('2018-02-16', 'a'),('2018-02-15', 'd'),
    ('2018-02-11', 'a'),('2018-02-10', 'e');

        SELECT * 
        FROM (
        SELECT a.*,WEEKDAY(date_column) weekday, WEEKDAY(CURDATE()) current_day 
        FROM data_table a
        ) b 
        WHERE 
        (b.weekday in (2,3,4) and current_day = 0)
        or (b.weekday in (3,4,0) and current_day = 1)
        or (b.weekday in (4,0,1) and current_day = 2)
        or (b.weekday < current_day && b.weekday >= current_day - 3);

Grabbing Data from Past 3 Business Days

1 Answers