CODEWITHSUNDEEP
sqloracle-databasegaps-and-islands
Jegan
Jegan

Reputation: 53

How to find month gaps in Oracle table?

I have a Oracle table which has EmpName (Char),Month_from and Month_to column (Numeric). Here I need to find missing months ( Month gaps). In the below sample data I have to find missing month 6 (Jun ).

Thanks in advance.

Sample Data:

|-------|-----------|--------|
|eName  |Month_From |Month_To|
|(Char) | ( Int)    | ( Int) |
|-------|------------|-------|
|John   |1          |2       | ( Jan to Feb)
|John   |3          |5       | ( Mar to May)
|John   |7          |8       | ( Jul to Aug)    
|-------|------------|-------|

Need to Find (Jun to Jun).

Upvotes: 3

Views: 245

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65158

Just conversion for the sample data, you may consider :

select to_char(to_date(lpad(t.month_from,2,'0'),'mm'),'Mon')||' to '||
       to_char(to_date(lpad(t.month_to,2,'0'),'mm'),'Mon')
  from my_table t
 where upper(t.eName) = upper('&i_eName');

For the question ( Jun to Jun ):

select to_char(to_date(lpad(a1.mon,2,'0'),'mm'),'Mon')
  from ( select level mon from dual connect by level <= 12 ) a1
 where not exists ( select null
                      from my_table a2
                     where a1.mon between a2.month_from and a2.month_to
                       and upper(a2.eName) = upper('&i_eName') )
 order by mon;

But, it returns also Sep, Oct, Nov, Dec, besides Jun. For this, i agree with @mathguy's comment.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Assuming no overlaps, you can find the missing months using lag():

select (prev_month_to + 1) as start_missing,
       (month_from - 1) as end_missing
from (select t.*, lag(month_to) over (partition by name order by month_from) as prev_month_to
      from t
     ) t
where prev_month_to <> month_from - 1;

This provides a range for each gap, because the gap could be more than one month.

Upvotes: 2

Related Questions