Musa almatri Musa almatri - 4 months ago 19
SQL Question

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

Answer

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