Fetch distinct available dates from table-CodePudding

I have a data table where data is present for every date (for 50 branches) except saturday and sunday. I am trying to get the last valid date from table from multiple given dates.

select distinct BW_DATE from backdated_weekly where BW_DATE <='2021-09-30' or BW_DATE<='2021-09-26' order by BW_DATE desc;

As 2021-09-30 is a valid date but 2021-09-26 (Sunday) is not present in table. I am trying to get output as

2021-09-30
2021-09-24

But above query gives all results below given dates.

CodePudding user response：

If it is confirmed there are dates continuously in the table for all mon-fri only, simply select the maximum date up to the given date

SELECT MAX(BW_DATE)
FROM backdated_weekly
WHERE BW_DATE <= '2021-09-30'
UNION
SELECT MAX(BW_DATE)
FROM backdated_weekly
WHERE BW_DATE <= '2021-09-26'

Also we can calculate newest date in mon-fri for a given date directly without any table

WEEKDAY is the function to be used

Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

SELECT CASE WHEN WEEKDAY('2021-09-30') IN ( 5, 6 ) THEN DATE('2021-09-30') - INTERVAL WEEKDAY('2021-09-30') - 4 DAY ELSE DATE('2021-09-30') END
UNION
SELECT CASE WHEN WEEKDAY('2021-09-26') IN ( 5, 6 ) THEN DATE('2021-09-26') - INTERVAL WEEKDAY('2021-09-26') - 4 DAY ELSE DATE('2021-09-26') END

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=477775c0eddbfa733e60bc629a8a68d4