DB2 Sql how to use the case statement?

Question

I am trying to extract all event end dates for events that expire in 2 working days. So for Friday and Thursday I want dates that end in 4 days or less, for Saturday dates that end in 3 days, for Sun-Wed dates that end within 2 days and +3 for all public holidays which are listed in column dateval in tempdate.

The script below isn't working, it only extact dates for +2 days. I understand I need to use a case statement, but am really not understanding how to implement that. Does someone have any ideas?

select event_end_date
from mdmins.table

/*separating by date to find expiring dates*/ 
where  (  (dayofweek(c.event_end_date) in (5,6) /*Friday & Thursday*/ 
and    date(c.event_end_date) < current_date + 4 days 
and    date(c.event_end_date) >= current_date) 
or     (dayofweek(c.event_end_date) in (1,2,3,4) /*All other days of week*/ 
and    date(c.event_end_date) < current_date + 2 days 
and    date(c.event_end_date) >= current_date) 
or     (dayofweek(c.event_end_date) = 7 /*Saturday - not that this should be needed, but just in case*/ 
and    date(c.event_end_date) < current_date + 3 days 
and    date(c.event_end_date) >= current_date) 
or     (date(c.event_end_date) < (select dateval from mdmins.tempdate where dateval = (current date + 3 days)) /*a holiday is on in 3 days, so 2 working days before*/ 
and    date(c.event_end_date) >= current_date) ) 

Answer 1

Logically, the only thing that varies between the data of the week is how far ahead you look, so limit the logic to that using a case:

...
where date(c.event_end_date) between current_date and current_date +
  case
    when dayofweek(c.event_end_date) in (5, 6) then 4
    when dayofweek(c.event_end_date) in (1, 2, 3, 4) then 2
    else 3
  end days
or (date(c.event_end_date) < (select dateval
  from mdmins.tempdate
  where dateval = (current date + 3 days)) 
  and date(c.event_end_date) >= current_date)

Answer 2

where ((date(c.event_end_date) between current_date and current_date +
  case
    when dayofweek(current date) in (5,6) then 4
    when dayofweek(current date) in (1, 2, 3, 4) then 2
    else 3
  end days)
  or    date(c.event_end_date) < (select dateval
      from mdmins.tempdate
      where dateval = (current date + 3 days)) 
      and date(c.event_end_date) >= current_date )

Answer 3

This is a classic example of why you want a calendar that includes all dates, with a bunch of extra information. You'd be able to do something similar to this:

WITH Desired_Dates AS (SELECT MIN(calendarDate) AS startRange,
                              MAX(calendarDate) + 1 DAY AS endRange
                       FROM(SELECT calendarDate
                            FROM Calendar
                            WHERE calendarDate >= CURRENT_DATE
                                  AND dayOfWeekISO IN (1, 2, 3, 4, 5)
                                  AND isHolday = '0'
                            ORDER BY calendarDate
                            FETCH FIRST 3 ROWS ONLY) D)
SELECT C.<column_list
FROM <Your_Table> C
JOIN Desired_Dates D
  ON C.event_end_date >= D.startRange
     AND C.event_end_Date < D.endRange

(Note that I'm specifically using the ISO day-of-week, where Monday is 1. Sunday as 1 is a US thing, and functions to derive day-of-week from a given date are subject to the whims of the current culture settings. Also, in the case that event_end_date is something like a timestamp, you want to use an exclusive end range
This actually has a decent chance of using index-only information to grab the desired dates (assuming a relevant index) and in any case will only grab the data in the CTE once.

If you don't have a calendar table, you can make one:

WITH Working_Dates AS (SELECT CURRENT_DATE - 1 DAY AS calendarDate, 
                              0 AS workingDayNum, '0' AS isWorkingDay
                       FROM SYSIBM/SYSDUMMY1
                       UNION ALL 
                       SELECT calendarDate, 
                              workingDayNum + CASE WHEN isWorkingDay = 1
                                                   THEN 1
                                                   ELSE 0 END AS workingDayNum,
                              isWorkingDay
                       FROM (SELECT calendarDate, workingDayNum,
                                    CASE WHEN DAYOFWEEK_ISO(WD.calendarDate) IN (6, 7)
                                              OR Holiday.dateval IS NOT NULL 
                                         THEN '0'
                                         ELSE '1' END AS isWorkingDay
                             FROM (SELECT calendarDate + 1 DAY, workingDayNum
                                   FROM Working_Dates
                                   WHERE workingDayNum < 2) WD
                             LEFT JOIN mdmins.tempdate Holiday
                                    ON Holiday.dateval = WD.calendarDate) WD),

     Desired_Dates AS (SELECT MIN(calendarDate) AS startRange,
                              MAX(calendarDate) + 1 DAY AS endRange
                       FROM Working_Date
                       WHERE isWorkingDay = '1')

SELECT C.<column_list
FROM <Your_Table> C
JOIN Desired_Dates D
  ON C.event_end_date >= D.startRange
     AND C.event_end_Date < D.endRange

This is using a recursive CTE to loop through days from (one before) the current date until three working days have passed. (I had to start it a day early so I only had to write the "is it a working day" logic once, which is also why all the nesting).

Note that neither your original attempts, nor any of the existing answers take these into account:

1. What happens if there's a holiday on a Friday or a Monday (5 days to skip, not 4)?
2. What happens if there are multiple holidays in sequence (unknown number of days to skip)?

By building a list of the actual working days, we can insulate ourselves from the problem.