Query to find the missing dates of a month in Oracle

Question

Here are the dates available in my Record_Date (Date) column in Attendance table in Oracle 10g.You can find the dates 04/06/2016 08/06/2016 16/06/2016 23/06/2016 29/06/2016 are missing in the sequence.

   **Record_Date**  

    01/06/2016
    02/06/2016
    03/06/2016
    05/06/2016
    06/06/2016
    07/06/2016
    09/06/2016
    10/06/2016
    12/06/2016
    13/06/2016
    14/06/2016
    15/06/2016
    17/06/2016
    18/06/2016
    19/06/2016
    20/06/2016
    21/06/2016
    22/06/2016
    24/06/2016
    25/06/2016
    26/06/2016
    27/06/2016
    28/06/2016
    30/06/2016
    01/07/2016

I just need a query to find the missing dates in the specific month (and later also in the Year).

Kindly show me an approach

My approach: Create a calendar table and left join it to the above table, retaining records in the former not matching to anything in the latter. — Tim Biegeleisen, Dec 01 '16 at 05:20
Do you have any identity column in the table so that we can query data accordingly. If not then you should have that. — Shushil Bohara, Dec 01 '16 at 05:31
Mybe that [**answer**](http://stackoverflow.com/questions/5635594/how-to-create-a-calender-table-for-100-years-in-sql) can help. — Ilyes, Dec 01 '16 at 05:31
@Suraz columns are RECORD_DATE NOT NULL DATE and LINE_NO NOT NULL NUMBER — code_Finder, Dec 01 '16 at 07:19

Wernfried Domscheit · Answer 1 · 2016-12-01T07:43:47.360

You can use this one:

WITH all_days AS 
    (SELECT DATE '2016-06-01' + LEVEL-1 AS the_day
    FROM dual
    CONNECT BY DATE '2016-06-01' + LEVEL-1 <= DATE '2016-06-30')
SELECT the_day
FROM all_days
WHERE the_day <>ALL (SELECT Record_Date FROM Attendance);

Or, if you like to have it more dynamically:

WITH all_days AS 
    (SELECT START_DATE + LEVEL AS the_day
    FROM dual
        CROSS JOIN 
            (SELECT 
                TRUNC(MIN(Record_Date), 'MM') -1 AS START_DATE, 
                TRUNC(LAST_DAY(MAX(Record_Date))) AS END_DATE 
            FROM Attendance)
    CONNECT BY START_DATE + LEVEL <= END_DATE)
SELECT the_day
FROM all_days
WHERE the_day <>ALL (SELECT Record_Date FROM Attendance);

Note, <>ALL is the same as NOT IN - it's just my personal preference.

score 0 · Answer 2 · answered Dec 01 '16 at 06:14

with 
nums(num )
as
(select 0 from dual
union all
select num + 1 from nums
  where num < (select max(col) from qtable)- (select min(col) from qtable)
),

date_btwn(dat) 
as(select num + (select min(col) from qtable) from nums)

select  dat from date_btwn
minus
select col from qtable;

Inline views nums will generate all numbers to add from start date. date_btwn contains all dates between start date and end date in table. We are excluding the dates in our table using minus.

This would fail for example if 01/06/2016 is missing in Attendance table, i.e. it would be be shown. — Wernfried Domscheit, Dec 01 '16 at 07:52

Mr. Bhosale · Answer 3 · 2016-12-01T07:56:21.643

You need to generate all dates and you have to find missing ones. Below with cte i have done it

Using CTE and not inQuery :

            with calendar as (
                    select rownum - 1 as daynum,
                    to_date('1-jun-2016') + (rownum - 1) as monthdate
                    from dual )
            select monthdate  as monthdate
            from calendar              
            where monthdate  not in (
            SELECT 
            Record_Date
            FROM Attendance      )  
             and monthdate   >=  to_date('1-jun-2016')  /* CHANGE as per need */
             and monthdate   <  to_date('1-jul-2016')  /* CHANGE as per need */

Or Using CTE and left join Query :

            with calendar as (
                    select rownum - 1 as daynum,
                    to_date('1-jun-2016') + (rownum - 1) as monthdate
                    from dual )
            select monthdate  as monthdate
            from calendar C left join Attendance A on A.Record_Date=c.monthdate
            where A.Record_Date is  null
             and monthdate   >=  to_date('1-jun-2016')  /* CHANGE as per need */
             and monthdate   <  to_date('1-jul-2016')  /* CHANGE as per need */

as per datecolumn format change it to validformat into selection query first then use this query.

Query to find the missing dates of a month in Oracle

3 Answers3