CODEWITHSUNDEEP
sqloracle-database
Musa almatri
Musa almatri

Reputation: 5842

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

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

Upvotes: 2

Views: 2084

Answers (3)

user3104783
user3104783

Reputation: 19

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

Upvotes: 0

krokodilko
krokodilko

Reputation: 36087

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

Upvotes: 2

Sara
Sara

Reputation: 19

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

Upvotes: 0

Related Questions