Oracle - find all dates not in the table.

Question

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

Answer 1

To receive all days off from 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 dates that are present in this table, simply add "and does not exist (select 1 from your_table y, where y.the_date = as_of_date)" or similar, for example:

 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 so as not to use a subquery, just change the values ​​of the "as_of_date" column to "sysdate + rownum-1" and you should be good

It is also worth noting that I used this query when I needed to get all the dates, not just the weekend. What I did in response simply excluded the weekend in most requests. If you do not want to return unnecessary data, I am sure that the internal request can be changed so as not to go through 365 days, but only through the number of days off (i.e. use where rownum <365/5 and get date + (6 7) starting on 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 arise a lot. Usually they include creating some sequence and displaying it.

Instead, if you can live by looking for a missing date following the number of missing dates in a string, the following works:

 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 assumes that the first date is present and that the data goes beyond the last date. Essentially, it finds the gaps between dates, then moves forward one week and calculates the length of the gap.