I have a dataset we can call 'jobs' which has one row for each job on our calendar. Row has a column that says if the job is occuring on a holiday or not. I want to filter on the individual card levels based on if there is a holiday tomorrow, 2 days from now, 3 days from now, etc.
Job # | JobDate | Holiday
1 | 9/3/18 | True
2 | 2/1/18 | False
3 | 5/4/18 | False
I am using this code as a filter on many cards to show "Tomorrow's jobs" or Sat-Mon in the case of Friday. However, I need to adjust this so that if tomorrow is a holiday to show both tomorrow and the next day. If the holiday happens on Monday and today is Friday I need to show all jobs from Sat-Tues.
-- When 'True', show tomorrow's jobs except on Fridays display upcoming Sat-Mon jobs, on Saturday's display upcoming Sun-Mon jobs.
case
when
(DAYOFWEEK(CURRENT_DATE())=1)
and (DATE(`JobDate`) = DATE(ADDDATE(CURRENT_DATE(), INTERVAL 1 DAY)))
then 'True'
-- On Sun show Mon
when
(DAYOFWEEK(CURRENT_DATE())=2)
and (DATE(`JobDate`) = DATE(ADDDATE(CURRENT_DATE(), INTERVAL 1 DAY)))
then 'True'
-- On Mon show Tue
when
(DAYOFWEEK(CURRENT_DATE())=3)
and (DATE(`JobDate`) = DATE(ADDDATE(CURRENT_DATE(), INTERVAL 1 DAY)))
then 'True'
-- on Tue show Wed
when
(DAYOFWEEK(CURRENT_DATE())=4)
and (DATE(`JobDate`) = DATE(ADDDATE(CURRENT_DATE(), INTERVAL 1 DAY)))
then 'True'
-- On Wed show Thur
when
(DAYOFWEEK(CURRENT_DATE())=5)
and (DATE(`JobDate`) = DATE(ADDDATE(CURRENT_DATE(), INTERVAL 1 DAY)))
then 'True'
-- On Thur show Fri
when
(DAYOFWEEK(CURRENT_DATE())=6)
-- Change to 4 when holidays on Monday. 3 When Normal week.
and (DATE(`JobDate`) <= DATE(ADDDATE(CURRENT_DATE(), INTERVAL 3 DAY)))
and (DATE(`JobDate`) > DATE(ADDDATE(CURRENT_DATE(), INTERVAL 0 DAY)))
then 'True'
-- on Fri show upcoming sat, sun, mon
when
(DAYOFWEEK(CURRENT_DATE())=7)
and (DATE(`JobDate`) <= DATE(ADDDATE(CURRENT_DATE(), INTERVAL 2 DAY)))
and (DATE(`JobDate`) > DATE(ADDDATE(CURRENT_DATE(), INTERVAL 0 DAY)))
then 'True'
-- on Sat show upcoming sun, mon
else 'False' end
Thank you in advance for the help!
