问如何在月度数据序列中查找丢失的日期？

EN

  WITH StartingPoints AS
     (SELECT ID, OCCURRANCE, ROW_NUMBER() OVER(ORDER BY id, OCCURRANCE) AS rn
        FROM dates_test A
       WHERE NOT EXISTS (SELECT *
                FROM dates_test B
               WHERE B.ID = A.ID
                 AND EXTRACT(month FROM B.OCCURRANCE) =
                     EXTRACT(month FROM A.OCCURRANCE) - 1
                 and EXTRACT(year FROM B.OCCURRANCE) =
                     EXTRACT(year FROM A.OCCURRANCE))),
    EndingPoints AS
     (SELECT ID, OCCURRANCE, ROW_NUMBER() OVER(ORDER BY id, OCCURRANCE) AS rn
        FROM dates_test A
       WHERE NOT EXISTS (SELECT *
                FROM dates_test B
               WHERE B.ID = A.ID
                 AND EXTRACT(month FROM B.OCCURRANCE) =
                     EXTRACT(month FROM A.OCCURRANCE) + 1
                 and EXTRACT(year FROM B.OCCURRANCE) =
                     EXTRACT(year FROM A.OCCURRANCE)))
    SELECT S.ID,
           EXTRACT(month FROM S.OCCURRANCE) AS start_range,
           EXTRACT(month FROM E.OCCURRANCE) AS end_range
      FROM StartingPoints S
      JOIN EndingPoints E
        ON E.ID = S.ID
       AND E.rn = S.rn;

                        WITH StartingPoints AS
     (SELECT ID, OCCURRANCE, ROW_NUMBER() OVER(ORDER BY id, OCCURRANCE) AS rn
        FROM dates_test A
       WHERE NOT EXISTS (SELECT *
                FROM dates_test B
               WHERE B.ID = A.ID
                 AND EXTRACT(month FROM B.OCCURRANCE) =
                     EXTRACT(month FROM A.OCCURRANCE) - 1
                 and EXTRACT(year FROM B.OCCURRANCE) =
                     EXTRACT(year FROM A.OCCURRANCE))),
    EndingPoints AS
     (SELECT ID, OCCURRANCE, ROW_NUMBER() OVER(ORDER BY id, OCCURRANCE) AS rn
        FROM dates_test A
       WHERE NOT EXISTS (SELECT *
                FROM dates_test B
               WHERE B.ID = A.ID
                 AND EXTRACT(month FROM B.OCCURRANCE) =
                     EXTRACT(month FROM A.OCCURRANCE) + 1
                 and EXTRACT(year FROM B.OCCURRANCE) =
                     EXTRACT(year FROM A.OCCURRANCE))),
    MissingPoints AS
     (SELECT S.ID,
             EXTRACT(month FROM S.OCCURRANCE) AS start_range,
             EXTRACT(month FROM E.OCCURRANCE) AS end_range,
             EXTRACT(YEAR FROM E.OCCURRANCE) YEAR_of_OCCR
        FROM StartingPoints S
        JOIN EndingPoints E
          ON E.ID = S.ID
         AND E.rn = S.rn),
    i1 as
     (select level num from dual connect by level <= 12),
    ms11 as
     (select ID,
             start_range,
             end_range,
             lead(start_range, 1, 0) OVER(ORDER BY id, year_of_occr, start_range, end_range) as am_i_ms,
             lead(year_of_occr, 1, 0) OVER(ORDER BY id, year_of_occr) as miss_year,
             year_of_occr
        from MissingPoints),
    miss_month1 as
     (select id,
             start_range,
             end_range,
             DECODE(end_range + num, 13, 0, 14, 0, end_range + num) missing_month,
             year_of_occr
        from ms11, i1
       where ((end_range + num < am_i_ms or sTART_Range = end_range) and
             end_range + num <= 14) or (year_of_occr<> miss_year and am_i_ms >=0 and  am_i_ms <=12 and end_range + num <= 14)
       order by year_of_occr, missing_month),
    miss_month as
     (select *
        from miss_month1 A
       where not exists
       (select 1
                from miss_month1 B
               where A.ID = B.ID
                 AND (A.missing_month = B.start_range AND
                     A.missing_month = B.end_range)
                 and A.year_of_occr = B.year_of_occr)
         and decode(end_range, 12, -1, end_range) < missing_month),
    StartingmisPoints AS
     (SELECT A.*,
             ROW_NUMBER() OVER(ORDER BY id, year_of_occr, end_range, missing_month) AS rn
        FROM miss_month A
       WHERE NOT EXISTS (SELECT *
                FROM miss_month B
               WHERE B.ID = A.ID
                 and b.start_range = a.start_range
                 and b.end_range = a.end_range
                 AND B.missing_month = A.missing_month - 1
                 and b.year_of_occr = a.year_of_occr)),
    EndingmisPoints AS
     (SELECT A.*,
             ROW_NUMBER() OVER(ORDER BY id, year_of_occr, end_range, missing_month) AS rn
        FROM miss_month A
       WHERE NOT EXISTS (SELECT *
                FROM miss_month B
               WHERE B.ID = A.ID
                 AND B.missing_month = A.missing_month + 1
                 and b.start_range = a.start_range
                 and b.end_range = a.end_range
                 and b.year_of_occr = a.year_of_occr))
    SELECT distinct  S.ID,
           S.start_range,
           S.end_range,
           S.missing_month start_gap_range,
           E.missing_month end_gap_range,
           E.year_of_occr
      FROM StartingmisPoints S
      JOIN EndingmisPoints E
        ON E.ID = S.ID
       AND E.rn = S.rn

WITH StartingPoints AS
(
    SELECT ID, OCCURRANCE, ROW_NUMBER() OVER(ORDER BY OCCURRANCE) AS rn
    FROM dates_test A
    WHERE NOT EXISTS (
        SELECT *
        FROM dates_test B
        WHERE B.ID = A.ID AND EXTRACT(month FROM B.OCCURRANCE) = EXTRACT(month FROM A.OCCURRANCE) - 1)
),
EndingPoints AS
(
    SELECT ID, OCCURRANCE, ROW_NUMBER() OVER(ORDER BY OCCURRANCE) AS rn
    FROM dates_test A
    WHERE NOT EXISTS (
        SELECT *
        FROM dates_test B
        WHERE B.ID = A.ID AND EXTRACT(month FROM B.OCCURRANCE) = EXTRACT(month FROM A.OCCURRANCE) - 1)
)
SELECT S.ID, EXTRACT(month FROM S.OCCURRANCE) AS start_range, EXTRACT(month FROM E.OCCURRANCE) AS end_range
FROM StartingPoints S
JOIN EndingPoints E ON E.ID = S.ID AND E.rn = S.rn;