Case expression too complex

Question

I need to create a working dataset that includes the next applicable date. Based on certain parameters this will be required to be a certain day of the week, but more than one weekday may be valid. To ensure the next date is selected, I've put together a nested case statement.

Some sample data:

Job:
+----+-------------+----------+----------+----------+
| ID | SERV_PERIOD | SERV_SUN | SERV_MON | SERV_TUE |
+----+-------------+----------+----------+----------+
|  1 | W           | Y        | N        | N        |
|  2 | W           | N        | N        | Y        |
|  3 | W           | Y        | N        | Y        |
|  4 | W           | N        | Y        | Y        |
|  5 | F           | Y        | N        | N        |
|  6 | F           | N        | N        | Y        |
|  7 | F           | Y        | N        | Y        |
|  8 | F           | N        | Y        | Y        |
+----+-------------+----------+----------+----------+
Service:
+----+--------+------------+
| ID | JOB_ID |    DATE    |
+----+--------+------------+
|  1 |      1 | 2019-24-03 |
|  2 |      2 | 2019-26-03 |
|  3 |      3 | 2019-24-03 |
|  4 |      4 | 2019-26-03 |
|  5 |      5 | 2019-24-03 |
|  6 |      6 | 2019-26-03 |
|  7 |      7 | 2019-24-03 |
|  8 |      8 | 2019-26-03 |
+----+--------+------------+
Desired result: (one NextServiceDate for each Job)
+--------+-------------------------------+
| JOB.ID |        NextServiceDate        |
+--------+-------------------------------+
|      1 | 2019-31-03 (the next Sunday)  |
|      2 | 2019-02-04 (the next Tuesday) |
|      3 | 2019-26-03 (the next Tuesday) |
|      4 | 2019-01-04 (the next Monday)  |
|      5 | 2019-07-04 (2 Sundays ahead)  |
|      6 | 2019-09-04 (2 Tuesdays ahead) |
|      7 | 2019-02-04 (the next Tuesday) |
|      8 | 2019-08-04 (2 Mondays ahead)  |
+--------+-------------------------------+

Because multiple weekdays can be valid, the order in which their validity is checked is important: if Job 3 checks SERV_SUN first, finds that Sundays are valid, and therefore Selects the next Sunday date, Tuesday 26th would be missed. The fact that jobs can be either weekly or fortnightly adds an extra layer of complexity.

Not surprisingly, the following monster of a case statement is considered too deep:

CASE 
    WHEN J.SERV_PERIOD = 'W' THEN CASE
        WHEN DATEPART(DW,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID)) = 1 THEN CASE
            WHEN SERV_MON = Y THEN DATEADD(DD,1,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_TUE = Y THEN DATEADD(DD,2,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_WED = Y THEN DATEADD(DD,3,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_THU = Y THEN DATEADD(DD,4,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_FRI = Y THEN DATEADD(DD,5,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SAT = Y THEN DATEADD(DD,6,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SUN = Y THEN DATEADD(DD,7,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
        WHEN DATEPART(DW,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID)) = 2 THEN CASE
            WHEN SERV_TUE = Y THEN DATEADD(DD,1,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_WED = Y THEN DATEADD(DD,2,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_THU = Y THEN DATEADD(DD,3,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_FRI = Y THEN DATEADD(DD,4,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SAT = Y THEN DATEADD(DD,5,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SUN = Y THEN DATEADD(DD,6,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_MON = Y THEN DATEADD(DD,7,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
        WHEN DATEPART(DW,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID)) = 3 THEN CASE
            WHEN SERV_WED = Y THEN DATEADD(DD,1,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_THU = Y THEN DATEADD(DD,2,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_FRI = Y THEN DATEADD(DD,3,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SAT = Y THEN DATEADD(DD,4,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SUN = Y THEN DATEADD(DD,5,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_MON = Y THEN DATEADD(DD,6,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_TUE = Y THEN DATEADD(DD,7,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
        WHEN DATEPART(DW,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID)) = 4 THEN CASE
            WHEN SERV_THU = Y THEN DATEADD(DD,1,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_FRI = Y THEN DATEADD(DD,2,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SAT = Y THEN DATEADD(DD,3,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SUN = Y THEN DATEADD(DD,4,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_MON = Y THEN DATEADD(DD,5,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_TUE = Y THEN DATEADD(DD,6,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_WED = Y THEN DATEADD(DD,7,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
        WHEN DATEPART(DW,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID)) = 5 THEN CASE
            WHEN SERV_FRI = Y THEN DATEADD(DD,1,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SAT = Y THEN DATEADD(DD,2,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SUN = Y THEN DATEADD(DD,3,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_MON = Y THEN DATEADD(DD,4,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_TUE = Y THEN DATEADD(DD,5,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_WED = Y THEN DATEADD(DD,6,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_THU = Y THEN DATEADD(DD,7,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
        WHEN DATEPART(DW,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID)) = 6 THEN CASE
            WHEN SERV_SAT = Y THEN DATEADD(DD,1,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SUN = Y THEN DATEADD(DD,2,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_MON = Y THEN DATEADD(DD,3,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_TUE = Y THEN DATEADD(DD,4,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_WED = Y THEN DATEADD(DD,5,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_THU = Y THEN DATEADD(DD,6,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_FRI = Y THEN DATEADD(DD,7,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
        WHEN DATEPART(DW,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID)) = 7 THEN CASE
            WHEN SERV_SUN = Y THEN DATEADD(DD,1,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_MON = Y THEN DATEADD(DD,2,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_TUE = Y THEN DATEADD(DD,3,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_WED = Y THEN DATEADD(DD,4,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_THU = Y THEN DATEADD(DD,5,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_FRI = Y THEN DATEADD(DD,6,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SAT = Y THEN DATEADD(DD,7,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
    WHEN J.SERV_PERIOD = 'F' THEN CASE
        WHEN DATEPART(DW,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID)) = 1 THEN CASE
            WHEN SERV_MON = Y THEN DATEADD(DD,8,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_TUE = Y THEN DATEADD(DD,9,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_WED = Y THEN DATEADD(DD,10,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_THU = Y THEN DATEADD(DD,11,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_FRI = Y THEN DATEADD(DD,12,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SAT = Y THEN DATEADD(DD,13,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SUN = Y THEN DATEADD(DD,14,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
        WHEN DATEPART(DW,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID)) = 2 THEN CASE
            WHEN SERV_TUE = Y THEN DATEADD(DD,8,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_WED = Y THEN DATEADD(DD,9,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_THU = Y THEN DATEADD(DD,10,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_FRI = Y THEN DATEADD(DD,11,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SAT = Y THEN DATEADD(DD,12,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SUN = Y THEN DATEADD(DD,13,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_MON = Y THEN DATEADD(DD,14,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
        WHEN DATEPART(DW,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID)) = 3 THEN CASE
            WHEN SERV_WED = Y THEN DATEADD(DD,8,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_THU = Y THEN DATEADD(DD,9,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_FRI = Y THEN DATEADD(DD,10,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SAT = Y THEN DATEADD(DD,11,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SUN = Y THEN DATEADD(DD,12,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_MON = Y THEN DATEADD(DD,13,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_TUE = Y THEN DATEADD(DD,14,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
        WHEN DATEPART(DW,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID)) = 4 THEN CASE
            WHEN SERV_THU = Y THEN DATEADD(DD,8,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_FRI = Y THEN DATEADD(DD,9,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SAT = Y THEN DATEADD(DD,10,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SUN = Y THEN DATEADD(DD,11,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_MON = Y THEN DATEADD(DD,12,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_TUE = Y THEN DATEADD(DD,13,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_WED = Y THEN DATEADD(DD,14,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
        WHEN DATEPART(DW,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID)) = 5 THEN CASE
            WHEN SERV_FRI = Y THEN DATEADD(DD,8,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SAT = Y THEN DATEADD(DD,9,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SUN = Y THEN DATEADD(DD,10,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_MON = Y THEN DATEADD(DD,11,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_TUE = Y THEN DATEADD(DD,12,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_WED = Y THEN DATEADD(DD,13,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_THU = Y THEN DATEADD(DD,14,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
        WHEN DATEPART(DW,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID)) = 6 THEN CASE
            WHEN SERV_SAT = Y THEN DATEADD(DD,8,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SUN = Y THEN DATEADD(DD,9,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_MON = Y THEN DATEADD(DD,10,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_TUE = Y THEN DATEADD(DD,11,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_WED = Y THEN DATEADD(DD,12,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_THU = Y THEN DATEADD(DD,13,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_FRI = Y THEN DATEADD(DD,14,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
        WHEN DATEPART(DW,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID)) = 7 THEN CASE
            WHEN SERV_SUN = Y THEN DATEADD(DD,8,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_MON = Y THEN DATEADD(DD,9,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_TUE = Y THEN DATEADD(DD,10,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_WED = Y THEN DATEADD(DD,11,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_THU = Y THEN DATEADD(DD,12,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_FRI = Y THEN DATEADD(DD,13,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
            WHEN SERV_SAT = Y THEN DATEADD(DD,14,(SELECT MAX(DATE) FROM SERVICE WHERE JOB_ID = J.ID))
END AS NextServiceDate

I have to cycle through the SERV_ fields in a different order depending on what weekday the last service was done on, so that I avoid skipping a valid date. How do I avoid this problem? Is there a simpler approach that I'm missing?

Though my error is identical to this one I don't see a way to eliminate the nesting as was the solution there.

can you add desired results for what you mean by 'fortnightly' to know how different is that calculation from a 'weekly' one? — sabhari karthik, Apr 08 '19 at 07:21
It is not clear from your description and example whether you want one result row per service or per job. Please clarify. — Thorsten Kettner, Apr 08 '19 at 07:43

score 3 · Answer 1 · answered Apr 08 '19 at 07:53

Break it down, build little CTE's that help you get to the target:

declare @Job table (ID int,Serv_Period char(1), Serv_Sun char(1),
                           Serv_mon char(1), Serv_Tue char(1))
insert into @Job(ID,SERV_PERIOD,SERV_SUN,SERV_MON,SERV_TUE) values
(1,'W','Y','N','N'),
(2,'W','N','N','Y'),
(3,'W','Y','N','Y'),
(4,'W','N','Y','Y')
declare @Service table (ID int, Job_id int, Date date)
insert into @Service(ID,JOB_ID,DATE) values
(1,1,'20190324'),
(2,2,'20190326'),
(3,3,'20190324'),
(4,4,'20190326')
--Desired result:
--+--------+-------------------------------+
--| JOB.ID |        NextServiceDate        |
--+--------+-------------------------------+
--|      1 | 2019-31-03 (the next Sunday)  |
--|      2 | 2019-02-04 (the next Tuesday) |
--|      3 | 2019-26-03 (the next Tuesday) |
--|      4 | 2019-01-04 (the next Monday)  |
--+--------+-------------------------------+

;With Recent as (
    select Job_ID,MAX(Date) as Recent from @Service group by Job_ID
), Numbers as (
    select 1 as n union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6 union all select 7
), Possibles as (
    select
        Job_ID,DATEADD(day,n,Recent) as PossibleDate
    from
        Recent r
            cross join
        Numbers n
    --Where clause if it's a real numbers table
)
select
    j.ID,
    MIN(PossibleDate)
from
    @Job j
        inner join
    Possibles p
        on
            j.ID = p.Job_id
where
    (j.Serv_Sun = 'Y' and DATEPART(weekday,PossibleDate) = DATEPART(weekday,'20150104')) or
    (j.Serv_Mon = 'Y' and DATEPART(weekday,PossibleDate) = DATEPART(weekday,'20150105')) or
    (j.Serv_Tue = 'Y' and DATEPART(weekday,PossibleDate) = DATEPART(weekday,'20150106'))
group by j.ID

Results:

ID          
----------- ----------
1           2019-03-31
2           2019-04-02
3           2019-03-26
4           2019-04-01

So, what do we do? Recent just finds our start dates. Numbers gives us the numbers from 1 - 7 (if you have a real numbers table in your system, skip that one).

PossibleDates combines those to add days to Recent.

Then we join this to our jobs table, filter down to all of the rows which satisfy the servicing conditions, and then (via GROUP BY and MIN) pick the earliest of these dates as our result.

At the end I'm filtering down the weekdays by comparing against "known good" dates. I do this to avoid any dependencies on the current DATEFIRST setting.

You might extend this by having multiple CTEs, one for each type of serving period and LEFT JOINing to each of them based on the period.

Ivan Starostin · Answer 2 · 2019-04-08T11:07:59.137

Step 1

Get rid of repeating subquery:

 /* same case with MAX_DATE instead of subquery */
FROM ...
CROSS APPLY (SELECT MAX(DATE) MAX_DATE FROM SERVICE WHERE JOB_ID = J.ID) d

possible version

FROM ...
INNER JOIN (SELECT MAX(DATE) MAX_DATE, JOB_ID FROM SERVICE S GROUP BY S.JOB_ID) d
ON D.JOB_ID = J.ID

if you are doing this for a single job - choose version 1, if for all jobs - version 2 is preferred.

Step 2

Systemize primitive arithmetics from nested cases:

;WITH cteJobs As (
SELECT
    CASE
        WHEN SERV_MON = 'Y' THEN 1
        WHEN SERV_TUE = 'Y' THEN 2
        WHEN SERV_WED = 'Y' THEN 3
        WHEN SERV_THU = 'Y' THEN 4
        WHEN SERV_FRI = 'Y' THEN 5
        WHEN SERV_SAT = 'Y' THEN 6
        WHEN SERV_SUN = 'Y' THEN 7
    END DW,
    *
    FROM JOBS J
)
SELECT
    CASE
       WHEN J.SERV_PERIOD = 'W' THEN
          CASE 
          WHEN DATEPART(DW, d.MAX_DATE) >= j.DW 
          /* "fill" till the end of week, then add supported DW */
          THEN DATEADD(DD, 7 - DATEPART(DW, d.MAX_DATE) + j.DW + 1, d.MAX_DATE)
          /* add delta between last date (Mon) and next avail date within this week (Tue) (delta = 1) */
          ELSE DATEADD(DD, j.DW - DATEPART(DW, d.MAX_DATE) + 1, d.MAX_DATE)
          END
       WHEN J.SERV_PERIOD = 'F' THEN
           ...
    END NextServiceDate
FROM cteJobs J
CROSS APPLY (SELECT MAX(DATE) MAX_DATE FROM SERVICE WHERE JOB_ID = J.ID) d

Step 3

Turn SERV_PERIOD into number as well:

;WITH cteJobs As (
SELECT
    CASE
        WHEN SERV_MON = 'Y' THEN 1
        WHEN SERV_TUE = 'Y' THEN 2
        WHEN SERV_WED = 'Y' THEN 3
        WHEN SERV_THU = 'Y' THEN 4
        WHEN SERV_FRI = 'Y' THEN 5
        WHEN SERV_SAT = 'Y' THEN 6
        WHEN SERV_SUN = 'Y' THEN 7
    END DW,
    CASE
       WHEN SERV_PERIOD = 'W' THEN 1
       WHEN SERV_PERIOD = 'F' THEN 8
    END SERV_PERIOD_INC,
    *
    FROM JOB J
)
SELECT
   J.*,
   d.MAX_DATE LastServiceDate,
   DATEPART(DW, d.MAX_DATE) LastWeekDay,
   CASE 
       /* if no avail date within this week */
       WHEN DATEPART(DW, d.MAX_DATE) >= j.DW 
       THEN DATEADD(DD, 7 - DATEPART(DW, d.MAX_DATE) + j.DW + J.SERV_PERIOD_INC, d.MAX_DATE)
       ELSE DATEADD(DD, j.DW - DATEPART(DW, d.MAX_DATE) + J.SERV_PERIOD_INC, d.MAX_DATE)
   END NextServiceDate,
   DATEPART(DW, CASE 
       WHEN DATEPART(DW, d.MAX_DATE) >= j.DW 
       THEN DATEADD(DD, 7 - DATEPART(DW, d.MAX_DATE) + j.DW + J.SERV_PERIOD_INC, d.MAX_DATE)
       ELSE DATEADD(DD, j.DW - DATEPART(DW, d.MAX_DATE) + J.SERV_PERIOD_INC, d.MAX_DATE)
   END) NextWeekDay,
   J.SERV_PERIOD_INC
FROM cteJobs J
INNER JOIN (SELECT MAX(DATE) MAX_DATE, JOB_ID FROM SERVICE S GROUP BY S.JOB_ID) d
ON D.JOB_ID = J.ID

Step 4

Normalize your data to stop struggling with finding list of avail dates:

;WITH cteJobs AS (
  SELECT
    d_norm.DW_NUM AS DW,
    CASE
       WHEN SERV_PERIOD = 'W' THEN 1
       WHEN SERV_PERIOD = 'F' THEN 8
    END SERV_PERIOD_INC,
    *
    FROM JOB J
    CROSS APPLY (
      SELECT 1 AS DW_NUM
      WHERE J.SERV_MON = 'Y'
      UNION ALL
      SELECT 2
      WHERE J.SERV_TUE = 'Y'
      UNION ALL
      SELECT 3
      WHERE J.SERV_WED = 'Y'
      UNION ALL
      SELECT 4
      WHERE J.SERV_THU = 'Y'
      UNION ALL
      SELECT 5
      WHERE J.SERV_FRI = 'Y'
      UNION ALL
      SELECT 6
      WHERE J.SERV_SAT = 'Y'
      UNION ALL
      SELECT 7
      WHERE J.SERV_SUN = 'Y' 
    ) d_norm
)
SELECT
   J.ID JOB_ID,
   d.MAX_DATE LastServiceDate,
   MIN(CASE 
       WHEN DATEPART(DW, d.MAX_DATE) >= j.DW 
       THEN DATEADD(DD, 7 - DATEPART(DW, d.MAX_DATE) + j.DW + J.SERV_PERIOD_INC, d.MAX_DATE)
       ELSE DATEADD(DD, j.DW - DATEPART(DW, d.MAX_DATE) + J.SERV_PERIOD_INC, d.MAX_DATE)
   END) NextServiceDate
FROM cteJobs J
INNER JOIN (SELECT MAX(DATE) MAX_DATE, JOB_ID FROM SERVICE S GROUP BY S.JOB_ID) d
ON D.JOB_ID = J.ID
GROUP BY J.ID, d.MAX_DATE
ORDER BY J.ID

This fixes your code and makes possible to find nearest available date. Think of normalizing your data model. Available dates should rows, not columns.

done

| JOB_ID | LastServiceDate | NextServiceDate |
|--------|-----------------|-----------------|
|      1 |      2019-03-24 |      2019-03-31 |
|      2 |      2019-03-26 |      2019-04-02 |
|      3 |      2019-03-24 |      2019-03-26 |
|      4 |      2019-03-26 |      2019-04-01 |
|      5 |      2019-03-24 |      2019-04-07 |
|      6 |      2019-03-26 |      2019-04-09 |
|      7 |      2019-03-24 |      2019-04-02 |
|      8 |      2019-03-26 |      2019-04-08 |

your step 1 is a great idea I wish I'd have thought of, but your step 2 doesn't take into account multiple valid weekdays - if the job can be done both on Mondays and on Thursdays that solution will ignore potential Thursday dates, as it will have selected the 2nd case and skipped the rest. — Isaac Reefman, Apr 08 '19 at 07:33
Please take another look at this code. I don't understand what difference against your code you've found. Yes, CASE expression will succeed on first match. It does not care about "other potential matches". That's how your code works as well. — Ivan Starostin, Apr 08 '19 at 07:38
yes, but my code cycles through which of the SERV_ fields it checks first based on the weekday of the last service - so if the last service was a tuesday, it'll check to see if wednesday is a valid weekday first, instead of starting at Sunday and potentially leaving out a valid Wednesday-Saturday date. Then it starts again if the last service was a Wednesday, checking thursday first this time, etc... — Isaac Reefman, Apr 08 '19 at 07:42

Isaac Reefman · Answer 3 · 2019-04-09T06:19:43.603

While Damian The Unbeliever's answer and Ivan Starostin's answer both contained some good improvements to make the query more efficient and readable, the cause of the problem was that the inner nested CASE statements were not closed.

Tidying up with CTEs (as they suggested) makes the query itself much more readable, and using Damian's CROSS JOIN and UNION of literal numbers to reduce the space it takes up does even more. You can still use a CASE statement that size though, either as a subquery or a CTE; while its long and ungainly, it's not actually many levels deep, and works fine if you use proper syntax:

;WITH cteLastService AS (
    SELECT  MAX(DATE) AS LastServiceDate, 
            JOB_ID 
    FROM SERVICE GROUP BY SERVICE.JOB_ID
), cteWeekly AS (
    SELECT CASE J.SERV_PERIOD 
                WHEN 'F' THEN 7
                ELSE NULL 
            END AS 'WeekDayAdd',
            ID 
    FROM JOB AS J
), cteNextService AS (
    SELECT CASE 
        WHEN J.SERV_PERIOD IN ('W','F') THEN CASE
            WHEN DATEPART(DW,S.LastServiceDate) = 1 THEN CASE
                WHEN SERV_MON = 'Y' THEN DATEADD(DD,1 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_TUE = 'Y' THEN DATEADD(DD,2 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_WED = 'Y' THEN DATEADD(DD,3 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_THU = 'Y' THEN DATEADD(DD,4 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_FRI = 'Y' THEN DATEADD(DD,5 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_SAT = 'Y' THEN DATEADD(DD,6 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_SUN = 'Y' THEN DATEADD(DD,7 + W.WeekDayAdd,S.LastServiceDate)
            END
            WHEN DATEPART(DW,S.LastServiceDate) = 2 THEN CASE
                WHEN SERV_TUE = 'Y' THEN DATEADD(DD,1 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_WED = 'Y' THEN DATEADD(DD,2 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_THU = 'Y' THEN DATEADD(DD,3 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_FRI = 'Y' THEN DATEADD(DD,4 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_SAT = 'Y' THEN DATEADD(DD,5 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_SUN = 'Y' THEN DATEADD(DD,6 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_MON = 'Y' THEN DATEADD(DD,7 + W.WeekDayAdd,S.LastServiceDate)
            END
            WHEN DATEPART(DW,S.LastServiceDate) = 3 THEN CASE
                WHEN SERV_WED = 'Y' THEN DATEADD(DD,1 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_THU = 'Y' THEN DATEADD(DD,2 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_FRI = 'Y' THEN DATEADD(DD,3 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_SAT = 'Y' THEN DATEADD(DD,4 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_SUN = 'Y' THEN DATEADD(DD,5 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_MON = 'Y' THEN DATEADD(DD,6 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_TUE = 'Y' THEN DATEADD(DD,7 + W.WeekDayAdd,S.LastServiceDate)
            END
            WHEN DATEPART(DW,S.LastServiceDate) = 4 THEN CASE
                WHEN SERV_THU = 'Y' THEN DATEADD(DD,1 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_FRI = 'Y' THEN DATEADD(DD,2 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_SAT = 'Y' THEN DATEADD(DD,3 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_SUN = 'Y' THEN DATEADD(DD,4 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_MON = 'Y' THEN DATEADD(DD,5 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_TUE = 'Y' THEN DATEADD(DD,6 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_WED = 'Y' THEN DATEADD(DD,7 + W.WeekDayAdd,S.LastServiceDate)
            END
            WHEN DATEPART(DW,S.LastServiceDate) = 5 THEN CASE
                WHEN SERV_FRI = 'Y' THEN DATEADD(DD,1 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_SAT = 'Y' THEN DATEADD(DD,2 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_SUN = 'Y' THEN DATEADD(DD,3 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_MON = 'Y' THEN DATEADD(DD,4 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_TUE = 'Y' THEN DATEADD(DD,5 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_WED = 'Y' THEN DATEADD(DD,6 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_THU = 'Y' THEN DATEADD(DD,7 + W.WeekDayAdd,S.LastServiceDate)
            END
            WHEN DATEPART(DW,S.LastServiceDate) = 6 THEN CASE
                WHEN SERV_SAT = 'Y' THEN DATEADD(DD,1 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_SUN = 'Y' THEN DATEADD(DD,2 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_MON = 'Y' THEN DATEADD(DD,3 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_TUE = 'Y' THEN DATEADD(DD,4 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_WED = 'Y' THEN DATEADD(DD,5 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_THU = 'Y' THEN DATEADD(DD,6 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_FRI = 'Y' THEN DATEADD(DD,7 + W.WeekDayAdd,S.LastServiceDate)
            END
            WHEN DATEPART(DW,S.LastServiceDate) = 7 THEN CASE
                WHEN SERV_SUN = 'Y' THEN DATEADD(DD,1 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_MON = 'Y' THEN DATEADD(DD,2 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_TUE = 'Y' THEN DATEADD(DD,3 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_WED = 'Y' THEN DATEADD(DD,4 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_THU = 'Y' THEN DATEADD(DD,5 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_FRI = 'Y' THEN DATEADD(DD,6 + W.WeekDayAdd,S.LastServiceDate)
                WHEN SERV_SAT = 'Y' THEN DATEADD(DD,7 + W.WeekDayAdd,S.LastServiceDate)
            END
        END
    END AS 'NextServiceDate',
    J.ID
    FROM JOB AS J INNER JOIN
        cteLastService AS S ON S.JOB_ID = J.ID INNER JOIN
        cteWeekly AS W ON W.ID = J.ID
)

Note the END keywords - without them, the original statement is nested 17 levels deep. With them its only 3. In this case good indenting made it look properly organized, but without telling each segment to close... And all that means it's as simple as this to put it all together:

SELECT  J.ID, 
        S.LastServiceDate
        N.NextServiceDate
FROM    JOB AS J LEFT JOIN
        cteLastService AS S ON J.ID = S.JOB_ID LEFT JOIN
        cteNextService AS N ON J.ID = N.ID

+----------------------------------------+
| ID | LastServiceDate | NextServiceDate |
+----|-----------------|-----------------+
|  1 |      2019-03-24 |      2019-03-31 |
|  2 |      2019-03-26 |      2019-04-02 |
|  3 |      2019-03-24 |      2019-03-26 |
|  4 |      2019-03-26 |      2019-04-01 |
|  5 |      2019-03-24 |      2019-04-07 |
|  6 |      2019-03-26 |      2019-04-09 |
|  7 |      2019-03-24 |      2019-04-02 |
|  8 |      2019-03-26 |      2019-04-08 |
+----+-----------------+-----------------+

Case expression too complex

3 Answers3

Step 1

Step 2

Step 3

Step 4