CODEWITHSUNDEEP
sqloracle-database
Adeel Aslam
Adeel Aslam

Reputation: 1294

Find missing dates using SQL

I have some dates in table over of a two years like this as a example date

01-jan-2012
02-jan-2012
04-jan-2012
05-jan-2012
06-jan-2012
07-jan-2012
09-jan-2012
11-jan-2012
.
.
.
01-DEC-2012

I think you have noticed that there a missing date of 03-jan-2012 and 08-jan-2012 and the same criteria with all dates.My Question is that is there any way in oracle to find the missing dates.Plz Help !

Upvotes: 3

Views: 6464

Answers (3)

Peter Lang
Peter Lang

Reputation: 55524

This will get you all missing days for one year (SQL Fiddle).

all_dates generates a list of all dates of 2012 (adjust as required), and the LEFT JOIN checking for IS NULL eliminates those dates that exist in your source-table.

WITH all_dates AS (
  SELECT TO_DATE('01-jan-2012') + ROWNUM - 1 AS d
  FROM dual
  CONNECT BY ROWNUM <= ADD_MONTHS(TO_DATE('01-jan-2012'), 12 ) - TO_DATE('01-jan-2012')
)
SELECT all_dates.d
FROM all_dates
LEFT JOIN t ON ( t.d = all_dates.d )
WHERE t.d IS NULL
ORDER BY all_dates.d
;

Make sure to use a bind variable instead of hard-coding the date three times.

Upvotes: 8

MontyPython
MontyPython

Reputation: 2994

Almost the same answer as @Peter, but a very slightly different version.

select all_dates.date_ missing_dates from 

(select to_date('01-Jan-14') + level - 1 date_
from dual connect by level <= 365) all_dates 

left join

((select to_date('01-Jan-14') + level - 1 date_
from dual connect by level <= 365)               

minus

(select to_date(round (dbms_random.value (1, 28))
        || '-'
        || round (dbms_random.value (01, 12))
        || '-'
        || round (dbms_random.value (2014, 2014)),
        'DD-MM-YYYY') + level - 1 random_date_1 
from dual
connect by level <= 52)) transaction_data

on all_dates.date_ = transaction_data.date_
where transaction_data.date_ is null;

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

You can generate sequence of date range you needed, then use LEFT JOIN to find missed dates.

Upvotes: 1

Related Questions