Сравнение дат, чтобы найти недостающие даты

Первый шаг — использовать LAG, как вы предложили, чтобы определить, когда кто-то больше не имеет права, и диапазон авторизации. Затем мы можем применить стратегию сравнения диапазонов, предложенную Сравнение диапазонов дат

WITH Previous as
(  
 SELECT *,
   LAG(MEM_EXP_DATE) OVER(PARTITION BY ID_NUM ORDER BY MEM_EFF_DATE,AUTH_EFF_DATE)  as PrevExprDate,
   LAG(AUTH_EFF_DATE) OVER(PARTITION BY ID_NUM ORDER BY MEM_EFF_DATE,AUTH_EFF_DATE)  as PrevAUTH_EFF_DATE,
   LAG(AUTH_EXP_DATE) OVER(PARTITION BY ID_NUM ORDER BY MEM_EFF_DATE,AUTH_EFF_DATE)  as PrevAUTH_EXP_DATE
 FROM ADMITS
),
Eligible as
(  
SELECT *,
    CASE 
     WHEN PrevExprDate IS NULL THEN NULL 
     WHEN PrevExprDate = '12/31/9999' THEN '12/31/9999'
     ELSE DateAdd(day, 1, PrevExprDate) END as StartNotEligible,
    CASE 
     WHEN MEM_EFF_DATE IS NULL THEN NULL 
     WHEN MEM_EFF_DATE = '12/31/9999' THEN '12/31/9999'
     ELSE DateAdd(day, -1, MEM_EFF_DATE) END as EndNotEligible
FROM Previous
)
Select ID_NUM, PrevAUTH_EFF_DATE, PrevAUTH_EXP_DATE, StartNotEligible,EndNotEligible 
FROM Eligible 
WHERE (StartNotEligible <= PrevAUTH_EXP_DATE      AND EndNotEligible >= PrevAUTH_EFF_DATE)

рабочий пример

Средний:

Финал:

Первым шагом является разделение и дедупликация данных авторизации и соответствия требованиям. Предоставленные исходные опубликованные данные представляют собой перекрестное применение этих двух данных, возможно, из более раннего запроса.

Второй шаг — рассчитать исключительные даты окончания, добавив 1 день к включенным датам окончания, указанным в данных. Вычисления диапазона дат и перекрытия работают лучше, если сочетать включающие даты начала и исключительные даты окончания, которые образуют полуоткрытые интервалы. Комбинация NULLIF() и ISNULL() используется для сохранения особых значений конца времени 9999-12-31.

В приведенном ниже коде я использую термин ThruDate для обозначения включенной даты окончания (только для отображения) и ToDate для обозначения исключительной даты окончания (используется логикой).

Далее нам нужно определить все отдельные диапазоны дат для тестирования. Это можно сделать, выделив все отдельные даты начала и окончания, отсортировав их, а затем создав новые диапазоны дат для каждого интервала. Функция окна LAG() используется для объединения предыдущей даты с текущими данными для определения каждого диапазона.

Теперь мы можем проверить каждый интервал дат на предмет совпадения с диапазонами дат авторизации и правомочности. Стандартный тест на перекрытие диапазонов дат — Start1 < End2 AND Start2 < End1. Если наш интервал выборки перекрывает строку авторизации, но не перекрывает строку приемлемости, мы определили разрешенный, но недопустимый интервал, который следует включить в результаты.

Все расчеты производятся независимо для каждого значения идентификатора.

Итоговая логика будет примерно такой:

-- Date range operations work much better when using "exclusive" end dates,
-- so we will calculate and use exclusive end dates for all range and overlap
-- logic, and only use the inclusive end dates for display.
--
-- To see the intermediate results:
--    Change the select to SELECT * and
--    Uncomment the WHERE clause.
WITH AuthDates AS (
    SELECT DISTINCT -- Distinct is used here to dedup the original unnormalized data
        ID_NUM AS Id,
        AUTH_EFF_DATE AS AuthFromDate,
        AUTH_EXP_DATE AS AuthThruDate, -- Inclusive
        ISNULL(
            DATEADD(day, 1, NULLIF(AUTH_EXP_DATE, '9999-12-31')),
            '9999-12-31') AS AuthToDate -- Exclusive
    FROM ADMITS
),
EligibilityDates AS ( -- Distinct is used here to dedup the original unnormalized data
    SELECT DISTINCT
        ID_NUM AS Id,
        MEM_EFF_DATE AS EligFromDate,
        MEM_EFF_DATE AS EligThruDate, -- Inclusive
        ISNULL(
            DATEADD(day, 1, NULLIF(MEM_EXP_DATE, '9999-12-31')),
            '9999-12-31') AS EligToDate -- Exclusive
    FROM ADMITS
),
DistinctDates AS (
    SELECT DISTINCT U.Id, U.Date
    FROM (
        SELECT A.Id, V.Date
        FROM AuthDates A
        CROSS APPLY (VALUES (AuthFromDate), (AuthToDate)) V(Date)
        UNION ALL  -- (Yes, I could drop the ALL, but I prefer an explicit DISTINCT)
        SELECT E.Id, V.Date
        FROM EligibilityDates E
        CROSS APPLY (VALUES (EligFromDate), (EligToDate)) V(Date)
    ) U
),
DateRanges AS (
    SELECT
        Id,
        LAG(Date) OVER(PARTITION BY Id ORDER BY Date)  AS FromDate,
        ISNULL(
            DATEADD(day, -1, NULLIF(Date, '9999-12-31')),
            '9999-12-31') AS ThruDate,
        Date AS ToDate
    FROM DistinctDates
)
SELECT
    A.Id,
    A.AuthFromDate, A.AuthThruDate,
    D.FromDate AS IneligibleFromDate, D.ThruDate AS IneligibleThruDate
FROM DateRanges D
LEFT JOIN AuthDates A
    ON A.Id = D.Id
    AND A.AuthFromDate < D.ToDate
    AND A.AuthToDate > D.FromDate
LEFT JOIN EligibilityDates E
    ON E.Id = D.Id
    AND E.EligFromDate < D.ToDate
    AND E.EligToDate > D.FromDate
WHERE A.ID IS NOT NULL  -- Authorized
AND E.Id IS NULL        -- But not eligible
ORDER BY
    D.Id,
    D.FromDate

(Текущие условия WHERE можно устранить, изменив первое LEFT JOIN на INNER JOIN и изменив второе LEFT JOIN на подвыборку WHERE NOT EXISTS(...), но я оставил их как внешние соединения, чтобы можно было легко просмотреть промежуточные результаты.)

Промежуточные результаты (с использованием исключительных дат окончания):

Окончательные результаты:

См. эту db<>fiddle для демонстрации, которая включает дополнительные тестовые данные, охватывающие несколько перекрывающихся сценариев авторизации и соответствия критериям.