Oracle - Find all dates that aren't in a table

Question

We have a table with a DATE column. How can we write a script which will return any weekend dates (Saturday or Sunday) over the next n years where we do NOT have the given weekend date in the table?

Answer 1

To get all weekends between today and today + 365 days:

select as_of_date
from
(
  select
    rownum,
    sysdate + rownum - 1 as as_of_date
  from dual
  connect by rownum <= (sysdate+365) - sysdate
) sub
where to_char(as_of_date, 'DY', 'nls_date_language=AMERICAN') in ('SAT', 'SUN')

To exclude the dates that are present in a given table, just add "and not exists (select 1 from your_table y where y.the_date = as_of_date)" or similar, like that:

select as_of_date
from
(
  select
    rownum,
    sysdate + rownum - 1 as as_of_date
  from dual
  connect by rownum <= (sysdate+365) - sysdate
) sub
where to_char(as_of_date, 'DY', 'nls_date_language=AMERICAN') in ('SAT', 'SUN')
      and not exists (select 1 from my_table myt where myt.as_of_date = sub.as_of_date)

It can be simplified to not use a subquery, just change the occurrances of the "as_of_date" column to "sysdate + rownum - 1" and you should be good

It is also worth noting that I used that query when I needed to get all dates, and not only weekends. What I did in the answer was just exclude the weekends in the outer most query. If you don't want to return unneeded data, I'm sure the inner most query can be changed to not go through 365 days, but only through the number of weekends (i.e. use where rownum < 365/5 and retrieve date + (6,7) starting in a saturday or sunday), but I don't think this is a big performance issue so I didn't worry about that

Answer 2

These types of questions come up a lot. They generally involve creating a sequence of some sort and mapping it back.

Instead, if you can live with finding a missing date following by the number of missing dates in a row, then something like the following will work:

select t.date+7, (nextdowdate - t.date)/7 as nummissing
from (select t.date, lead(t.date) over (partition by to_char(t.date, 'Dy') order by date) as nextdowdate
      from t
      where to_char(t.date, 'Dy') in ('Sat', 'Sun')
     ) t
where nextdowdate - t.date > 7 and
      date between FirstDate and LastDate

This does assume that the first date is present and that the data extends beyond the last date. Basically, it is finding gaps between dates, then moving forward one week and counting the length of the gap.