Find the first missing date in the (Oracle) column

Question

I need to find the first missing date in the date column from plan_table. which should not be in the holiday table or it should belong to any end of the week.

holiday_table saves all vacation dates.

Plan_table contains dates. here we must find the first missing date

Plan_id      Date 
1           10/2/2016
2           10/3/2016
3           10/6/2016
4           10/9/2016
5           10/10/2016
6           10/12/2016
7           10/13/2016
8           10/16/2016

Here the first missing date is 10/4/2016, but if this date is in the holiday table, we must show 10/5/2016 or the next first appearance.

Please help me write a request for the same.

Answer 1

you can use LEAD analytic function like

 select d
 from 
  (
    select
      date + 1  as d
    from 
    (
      select 
        date, 
        lead(date) over(order by date)  as next_date
        from 
        (
          select date   from plan_table
        union
          select date from holliday_table
         )
      order by date
    )
    where 
      trunc(date) + 1 < trunc(next_date)
    order by d 
  )
  where rownum = 1
 ;