Select Employee with no attendance

Databases
1

Hi Good day!

I have 2 tables for my dtr database.

first the employees where all the data of employee was save.

employees fields:

EmployeeID
Lastname
Firstname
Sub
Department

attendance_log
EMP_ID
LOG_TIME (datetime)
INDICATOR

I tried lots of query to get the EmployeeID where no attendance on the date I want to check who are the absentee.

I just want to select the EmployeeId where no LOG_TIME with the date 2013-05-02

attendance_log table is the table where the time in and out was save.

I hope somebody can help me to get only the EmployeeId with no attendance where Sub = ‘REG’ and Department IN (‘QA’, ‘Engineering’, ‘Assembly’).

Thank you

2

do you remember the LEFT OUTER JOIN with IS NULL check?

SELECT emp.employeeid 
  FROM employees AS emp
LEFT OUTER
  JOIN attendance_log AS att
    ON att.emp_id = emp.employeeid
   AND att.log_time = '2013-05-02'
 WHERE emp.sub = 'REG' 
   AND emp.department IN ('QA', 'Engineering', 'Assembly')
   AND att.emp_id IS NULL
3

Thank you for your suggested query. I tried it and my sql was not responding.

4

oh, that’s too bad :frowning:

5

It ends as not responding because i think the sql cannot load the query :frowning:

Thank you.

6

I tried this query:


SELECT emp.employeeid, emp.lastname, emp.firstname, emp.middlename, emp.department
  FROM employees AS emp
LEFT OUTER
  JOIN attendance_log AS att
    ON att.emp_id = emp.employeeid
   AND att.log_time = '2013-05-14'
 WHERE emp.sub = 'REG'
   AND emp.department = 'Assembly'
   AND att.emp_id IS NULL;

and sad to say all data from employees where department is Assembly was displayed even they had an attendance.

Thank you :frowning:

7

but none of them had an attendance on 2013-05-14, right?

8

Even if they had an attendance on 2013-05-14 ?

Edit: Never mind, I should post my answers a bit sooner… :smiley:

9

Hi,

This code is work to get the no IN and OUT for the particular date:


SELECT emp.employeeid, emp.lastname, emp.firstname, emp.middlename, emp.department
  FROM employees AS emp
LEFT OUTER
  JOIN attendance_log AS att
    ON att.emp_id = emp.employeeid
   AND DATE(att.log_time) = '2013-05-14'
 WHERE emp.sub = 'REG'
   AND emp.department IN ('Assembly', 'Fabrication', 'Compounding', 'Finishing', 'PET', 'Production', 'Squash', 'QA', 'ENGINEERING')
   AND att.emp_id IS NULL;

now i need a query to get only the employee with IN and OUT indicator:
also a query which the employee has only IN or has only OUT.

Thank you so much…

10

You’re welcome