I currently have a DATE view that looks like this:
| DATE | DAY | IS_WEEKEND |
|---|---|---|
| 05/27/24 | Mon | FALSE |
| 05/28/24 | Tues | FALSE |
| 05/29/24 | Wed | FALSE |
| 05/30/24 | Thur | FALSE |
| 06/01/24 | Fri | FALSE |
| 06/02/24 | Sat | TRUE |
| 06/03/24 | Sun | TRUE |
| 06/04/24 | Mon | FALSE |
I want to return the next WEEKDAY in the same table, kind of like this, preferably using a SQL query:
| DATE | DAY | IS_WEEKEND | NEXT_WEEK_DAY |
|---|---|---|---|
| 05/27/24 | Mon | FALSE | 05/28/24 |
| 05/28/24 | Tues | FALSE | 05/29/24 |
| 05/29/24 | Wed | FALSE | 05/30/24 |
| 05/30/24 | Thur | FALSE | 06/01/24 |
| 06/01/24 | Fri | FALSE | 06/04/24 |
| 06/02/24 | Sat | TRUE | 06/04/24 |
| 06/03/24 | Sun | TRUE | 06/04/24 |
| 06/04/24 | Mon | FALSE | 06/05/24 |
I think I’m close, but am not quite at the finish line. This is what I have thus far:
select date
, day
, is_weekend
, CASE
WHEN IS_WEEKEND = FALSE and DAY <> ' Fri' THEN DATEADD('DAY', 1, DATE)
WHEN IS_WEEKEND = TRUE or DAY = ' Fri' THEN
CASE
WHEN DAY = 'Fri' THEN DATEADD('DAY', 3, DATE)
ELSE DATEADD('DAY', 2, DATE)
END
END AS NEXT_WEEKDAY
from DATE t1;
Thank you very much in advance.