Isaac Reefman
Reputation: 597
Case expression too complex
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.
Upvotes: 1
Views: 190
Answers (3)
Isaac Reefman
Reputation: 597
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 |
+----+-----------------+-----------------+
Upvotes: 0
IVNSTN
Reputation: 9309
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.
| 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 |
Upvotes: 1
Damien_The_Unbeliever
Reputation: 239724
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.
Upvotes: 3
Related Questions
- How to re-write my CASE expression to avoid this specific error message?
- simplify a SQL case statement in a case expression
- CASE expression on multiple columns
- Define a CASE inside SQL Statement (Need simplification)
- Using Nested Case Expression
- Case expression with two columns
- Nested SQL case statement
- How to improve this SQL Server query with multiple 'CASE'?
- Excessive Case Statement Help - SQL Server
- Case statement trickery required