Étape 1

Débarrassez-vous de la sous-requête répétitive:

| 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 |

version possible

;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

si vous faites ceci pour un seul travail - choisissez la version 1, si pour tous les travaux - la version 2 est préférée.

Étape 2

Systématiser l'arithmétique primitive à partir de cas imbriqués:

;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

Étape 3

Transformez également SERV_PERIOD en nombre:

;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

Étape 4

Normaliser votre données pour arrêter de lutter pour trouver la liste des dates de disponibilité:

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

Cela corrige votre code et permet de trouver la date disponible la plus proche. Pensez à normaliser votre modèle de données. Les dates disponibles doivent être des lignes et non des colonnes.

done p>

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

Décomposez-le, construisez des petits CTE qui vous aident à atteindre la cible:

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

Résultats:

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

Alors, que faire Nous faisons? Récent trouve simplement nos dates de début. Numbers nous donne les nombres de 1 à 7 (si vous avez une table de nombres réels dans votre système, ignorez celle-ci).

PossibleDates combine ces pour ajouter des jours à Récent .

Ensuite, nous joignons ceci à notre table d'emplois, filtrons jusqu'à toutes les lignes qui satisfont aux conditions de service, puis (via GROUP BY et MIN ) choisissez la plus ancienne de ces dates comme résultat.

À la fin, je filtre les jours de la semaine en les comparant aux dates «connues comme bonnes». Je fais cela pour éviter toute dépendance sur le paramètre DATEFIRST actuel.

Vous pouvez étendre cela en ayant plusieurs CTE, un pour chaque type de période de diffusion et JOINT GAUCHE à chacun d'eux en fonction de la période.

Alors que la réponse de Damian The Unbeliever et La réponse d'Ivan Starostin contenait toutes deux de bonnes améliorations pour rendre la requête plus efficace et plus lisible, la cause du problème était que les instructions CASE imbriquées internes

Le rangement avec les CTE (comme ils l'ont suggéré) rend la requête elle-même beaucoup plus lisible, et l'utilisation des CROSS JOIN et UNION de Damian de nombres littéraux pour réduire l'espace qu'il prend en fait encore plus. Vous pouvez toujours utiliser une instruction CASE de cette taille, que ce soit comme sous-requête ou comme CTE; bien que ce soit long et disgracieux, ce n'est pas en fait beaucoup de niveaux profonds, et fonctionne bien si vous utilisez la syntaxe appropriée:

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 |
+----+-----------------+-----------------+

Notez les mots-clés END - sans eux, l'instruction d'origine est imbriquée 17 niveaux de profondeur . Avec eux, c'est seulement 3 . Dans ce cas, une bonne mise en retrait lui donnait l'air bien organisé, mais sans dire à chaque segment de se fermer ... Et tout cela signifie qu'il est aussi simple que cela de tout rassembler:

;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
)

p>