How to find missing data in a range of dates in oracle sql

Question

I have table (ATTENDANCE) contains the attendance of the employee as following

EMPNO   DATE
2     07/11/2016
2     07/12/2016
3     07/12/2016
6     07/13/2016
7     07/13/2016

which contains the employee number(empno) and the attended date(date)

And another table (EMPLOYEES) contains all the empolyees in the company for example like this

EMPNO   NAME
1       Musa
2       Ali 
3       Khalid
6       James
7       Sara

I can find if an employee was absent in a specific date by using this code

select empno 
from EMPLOYEES 
where empno not in (select empno 
               from ATTENDANCE
               where date = '07/11/2016')

I want to retreive the list of absent employees in any day between a specific range
I tried to use this query

select empno 
from EMPLOYEES 
where empno not in (select empno 
               from ATTENDANCE
               where  date between '07/11/2016' and  '07/13/2016'  )

but this will give me those who are absent in all the days in this range , If an employee was absent in one day only it will not retreive it

I want a query to retreive the empno and the date where the employee was absent?

Update: I tried this too , as sagi mentioned

select empno 
from EMPLOYEES 
where empno not in (select empno
    from ATTENDANCE
    group by empno
    having count(empno) = to_date(' 07/09/2016','mm,dd,rrrr') - to_date('07/13/2016','mm,dd,rrrr') )

this will work but it will not give me in which date the employee was absent

In your output do you need to distinguish between "week days" or "work days" on the one hand, and "weekend or holiday" on the other hand? I assume on a Sunday (in the U.S.) all employees would be "absent." — user5683823
– user5683823, Commented Jul 14, 2016 at 13:19

krokodilko · Accepted Answer · 2016-07-14 08:54:08Z

I want a query to retreive the empno and the date where the employee was absent

In such a case you need to generate all possible dates in a given range using for example this query:

SELECT date '2016-07-11' + level - 1 As "DATE" from dual
CONNECT BY LEVEL <= date '2016-07-13' - date '2016-07-11' + 1;

DATE            
-----------------
16/07/11 00:00:00
16/07/12 00:00:00
16/07/13 00:00:00

then use a cross join to generate all possible pairs: date+employe

SELECT e.empno, d."DATE"
FROM (
    SELECT date '2016-07-11' + level - 1 As "DATE" from dual
    CONNECT BY LEVEL <= date '2016-07-13' - date '2016-07-11' + 1
) d
CROSS JOIN empno e

and then filter existing records out (leaving only not existing pairs: empno+date) using an outer join and IS NULL condition

SELECT e.empno, d."DATE"
FROM (
    SELECT date '2016-07-11' + level - 1 As "DATE" from dual
    CONNECT BY LEVEL <= date '2016-07-13' - date '2016-07-11' + 1
  ) d
CROSS JOIN EMPLOYEES e 
LEFT JOIN ATTENDANCE a 
ON ( a.empno = e.empno AND a."DATE" = d."DATE" )
WHERE a."DATE" IS NULL
order by 1,2

Sara · Accepted Answer · 2016-07-14 08:43:56Z

0

select distinct empno from EMPLOYEES e, left join ATTENDANCE a on e.empno = a.empno and e.date=a.date where a.empno is NULL

answered Jul 14, 2016 at 8:43

Sara

195 bronze badges

1 Comment

Musa almatri Over a year ago

there is no date in he EMPLOYEES table

user3104783 · Accepted Answer · 2018-08-15 04:46:26Z

0

A much cleaner approach here would be to use a CTE to identify all distinct days in the attendance table.

we then do a Cross join with employees table to get all possible combinations

Finally we will check the existence of the EmpNo, Date tuple in the Attendance table.

With Dates (date)  as (Select Distinct Date from Attendance )

Select Emp.EmpNo, dates.date from employee emp cross join Dates
where (emp.empno, dates.date) not in
(Select Empno, date from Attendance

answered Aug 15, 2018 at 4:46

user3104783

192 bronze badges

Collectives™ on Stack Overflow

How to find missing data in a range of dates in oracle sql

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related