Select Missing Date Ranges In table In Oracle

Question

I have the following Data Table Data as:

EMPLOYEEID            DAYDATE
-----------------------------------
101                   01/08/2017
101                   02/08/2017
101                   04/08/2017
101                   06/08/2017
101                   07/08/2017
102                   01/08/2017
102                   03/08/2017
102                   06/08/2017

I want to Write a Query to get the following missing Dates between 01/8/2017 to 07/08/2017 Data as :

EMPLOYEEID            DAYDATE
-----------------------------------
101                   03/08/2017
101                   05/08/2017
101                   07/08/2017
102                   02/08/2017
102                   04/08/2017
102                   05/08/2017
102                   07/08/2017

How to deal with That?!

This has been answered here: https://stackoverflow.com/questions/18207721/find-missing-dates-from-date-range — kazzi, Sep 14 '17 at 12:33
Possible duplicate of [find missing dates from date range](https://stackoverflow.com/questions/18207721/find-missing-dates-from-date-range) — Ank, Sep 14 '17 at 12:35
@kazzi - not an exact duplicate, since here the OP also has different EMPLOYEEID, which complicates the problem. — , Sep 14 '17 at 12:48
Do you need to do this just for the two employees you showed? Or - MUCH BETTER! - do you have another table where ALL the EMPLOYEEID's are present (and EMPLOYEEID is primary key)? So, for example, what if there is EMPLOYEEID = 103, who is not present in your first table AT ALL - doesn't he have to be shown in the output, with ALL the dates considered to be missing? — , Sep 14 '17 at 12:50

score 0 · Answer 1 · answered Sep 14 '17 at 12:33

Either use a Date table, which I think is better, or use a Derived table to populate the dates(many codes available in google under "Oracle How to generate dates") :

SELECT t.employeeid,s.date
FROM (SELECT distinct p.employeeid FROM YourTable p) t
CROSS JOIN ( DATE TABLE \ Derived Table) s

This query will generate the entire range of dates for each employee. If you want only those that are missing:

SELECT t.employeeid,s.date
FROM (SELECT distinct p.employeeid FROM YourTable p) t
CROSS JOIN ( DATE TABLE \ Derived Table) s
LEFT JOIN YourTable h
 ON(h.employeeID = t.employeeId and h.date = s.date)
WHERE h.employeeID IS NULL

score 0 · Answer 2 · answered Sep 14 '17 at 12:54

You can generate all the rows with a hierarchical query:

SELECT DATE '2017-08-01' + LEVEL - 1 AS dt
FROM   DUAL
CONNECT BY DATE '2017-08-01' + LEVEL - 1 <= DATE '2017-08-07'

or a recursive sub-query factoring clause:

WITH alldates ( dt ) AS (
  SELECT DATE '2017-08-01' FROM DUAL
UNION ALL
  SELECT dt + 1 FROM alldates WHERE dt < DATE '2017-08-07'
)
SELECT * FROM alldates

Then you can use a partitioned outer join combine it with your existing data nad filter for those missing rows:

WITH alldates ( dt ) AS (
  SELECT DATE '2017-08-01' FROM DUAL
UNION ALL
  SELECT dt + 1 FROM alldates WHERE dt < DATE '2017-08-07'
)
SELECT employeeId,
       dt
FROM   alldates d
       LEFT OUTER JOIN
       your_table t
       PARTITION BY ( t.employeeID )
       ON ( t.daydate = d.dt )
WHERE  daydate IS NULL;

score 0 · Answer 3 · answered Sep 14 '17 at 12:58

Assuming you have a table EMPLOYEE in which EMPLOYEEID is primary key - and assuming that there may be employees who don't appear in your DATA_TABLE at all, and they must be then included in your output:

select e.employeeid, d.daydate
from   employee e cross join ( select date '2017-08-01' + level - 1 as daydate
                                 from dual
                                 connect by level <= 7
                             ) d
where  (e.employeeid, d.daydate) not in (select employeeid, daydate from data_table)
;

Select Missing Date Ranges In table In Oracle

3 Answers3