find gap between months in two consecutive year oracle sql

Question

Need to find record having gap between months in a table if the data is present in two different year. I have column like id, value,month, year.

Id, value, month,year
1,  123,    oct, 2020
1,  128,    nov, 2020
1,  127,    jan ,2021
2,  121,    Dec, 2020
2,   154,   jan,  2021   

Output I need: Id 1 as there is a gap in month (Dec is Missing for id=1)

Answer 1

I would construct a date and use lag():

select t.*
from (select t.*,
             lag(dte) over (partition by id order by dte) as prev_dte
      from (select t.*,
                   to_date(year || '-' || month || '-01', 'YYYY-MON-DD') as dte
            from t
           ) t
     ) t
where prev_dte <> dte - interval '1' month;

Here is a db<>fiddle.

Answer 2

Here's one option. Read comments within code.

SQL> with test (id, value, month, year) as
  2    -- sample data; you have that, don't type it
  3    (select 1, 123, 'oct', 2020 from dual union all
  4     select 1, 128, 'nov', 2020 from dual union all
  5     select 1, 127, 'jan', 2021 from dual union all
  6     select 2, 121, 'dec', 2020 from dual union all
  7     select 2, 154, 'jan', 2021 from dual
  8    ),
  9  temp as
 10    -- "convert" month and year to real date value
 11    (select id,
 12            value,
 13            to_date(month ||' '|| year, 'mon yyyy', 'nls_date_language=english') datum
 14     from test
 15    ),
 16  temp2 as
 17    -- select difference in months between DATUM and next month (LEAD!)
 18    (select id,
 19       months_between
 20         (datum,
 21          to_date(month ||' '|| year, 'mon yyyy', 'nls_date_language=english') datum
 22         ) diff
 23     from temp
 24    )
 25  select distinct id
 26  from temp2
 27  where abs(diff) > 1;

        ID
----------
         1

SQL>

It can probably be compressed, but step-by-step CTEs show what's going on.

Answer 3

Here is an example using the LAG function and finding rows where where the prior month is not one month behind (or non existent)

WITH
    sample_data (Id,
                 VALUE,
                 month,
                 year)
    AS
        (SELECT 1, 123, 'oct', 2020 FROM DUAL
         UNION ALL
         SELECT 1, 128, 'nov', 2020 FROM DUAL
         UNION ALL
         SELECT 1, 127, 'jan', 2021 FROM DUAL
         UNION ALL
         SELECT 2, 121, 'Dec', 2020 FROM DUAL
         UNION ALL
         SELECT 2, 154, 'jan', 2021 FROM DUAL)
SELECT DISTINCT id
  FROM (SELECT sd.id,
               CASE
                   WHEN    ADD_MONTHS (TO_DATE (sd.year || sd.month, 'YYYYMON'), -1) =
                           TO_DATE (
                               LAG (sd.year || sd.month)
                                   OVER (
                                       PARTITION BY id
                                       ORDER BY
                                           sd.year, EXTRACT (MONTH FROM TO_DATE (sd.month, 'MON'))),
                               'YYYYMON')
                        OR LAG (sd.id)
                               OVER (
                                   PARTITION BY id
                                   ORDER BY sd.year, EXTRACT (MONTH FROM TO_DATE (sd.month, 'MON')))
                               IS NULL
                   THEN
                       'Y'
                   ELSE
                       'N'
               END    AS valid_prev_month
          FROM sample_data sd)
 WHERE valid_prev_month = 'N';