Ytukyg

I often use this query1 to go back 6 days on a variable date:

query1 = SELECT

    DATE_FORMAT((DATE('2018-11-21') - INTERVAL(S.`I` - 1) DAY), '%Y-%m-%d')

    AS VAR

    FROM `HELPER_SEQ`

    AS S WHERE S.`I` <= 6;

With the help of this auxiliary table with a sequence inserted there.

CREATE TABLE `HELPER_SEQ` (`I` tinyint(3) UNSIGNED NOT NULL);

INSERT INTO `HELPER_SEQ` (`I`) VALUES (1),(2),(3),(4),(5),(6);

Question:

How can I avoid output weekends days using query1?
In a such way that the output go back in 6 useful days, in this example starting on '2018-11-21' the output would be ->

| 2018-11-21 | 

| 2018-11-20 |

| 2018-11-19 | 

| 2018-11-16 | 

| 2018-11-15 | 

| 2018-11-14 |

We can utilize DayName() function to get the name of the weekday corresponding to a Date. We will utilize this function's result to restrict weekends by NOT IN ('Saturday', 'Sunday').

Also, we will need to increase the number generator range upto 10. Because there is a possibility that we can come across 2 weekends (total 4 days) on either side of 5 weekdays.

So, we need 2 (first pair of weekend days) + 5 (weekdays) + 2 (second pair of weekend days) + 1 (6th weekday) = 10 dates to consider. An example of this edge case would be when an input date is Sunday.

We will need to use LIMIT 6 to restrict the result upto 6 days only, in the non-edge cases.

Schema (MySQL v5.7)

CREATE TABLE `HELPER_SEQ` (`I` tinyint(3) UNSIGNED NOT NULL);

INSERT INTO `HELPER_SEQ` (`I`) VALUES 

(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

Query

SELECT

    DATE_FORMAT((DATE('2018-11-21') - INTERVAL(S.`I` - 1) DAY), '%Y-%m-%d')

    AS VAR

FROM `HELPER_SEQ` AS S 

WHERE S.`I` <= 10 

  AND DAYNAME(DATE_FORMAT((DATE('2018-11-21') - INTERVAL(S.`I` - 1) DAY), '%Y-%m-%d')) NOT IN ('SATURDAY', 'SUNDAY')

ORDER BY VAR DESC

LIMIT 6;

Result

| VAR        |

| ---------- |

| 2018-11-21 |

| 2018-11-20 |

| 2018-11-19 |

| 2018-11-16 |

| 2018-11-15 |

| 2018-11-14 |

View on DB Fiddle

Edge Case Demo - Input date: 25 Nov 2018 (Sunday)

CREATE TABLE `HELPER_SEQ` (`I` tinyint(3) UNSIGNED NOT NULL);

INSERT INTO `HELPER_SEQ` (`I`) VALUES 

(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

Query #2

SELECT

    DATE_FORMAT((DATE('2018-11-25') - INTERVAL(S.`I` - 1) DAY), '%Y-%m-%d')

    AS VAR

FROM `HELPER_SEQ` AS S 

WHERE S.`I` <= 10 

  AND DAYNAME(DATE_FORMAT((DATE('2018-11-25') - INTERVAL(S.`I` - 1) DAY), '%Y-%m-%d')) NOT IN ('SATURDAY', 'SUNDAY')

ORDER BY VAR DESC

LIMIT 6;

Result

| VAR        |

| ---------- |

| 2018-11-23 |

| 2018-11-22 |

| 2018-11-21 |

| 2018-11-20 |

| 2018-11-19 |

| 2018-11-16 |

View on DB Fiddle