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)

question from:https://stackoverflow.com/questions/66052123/find-gap-between-months-in-two-consecutive-year-oracle-sql

Answer 1

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.