select employee where no attendance
Hi Good day!
I have 2 tables for my dtr database.
first the employees where all the data of employee was save.
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').
(make sure there's an index on LOG_TIME so that the query is efficient on large data sets)
SELECT EmployeeID FROM employees WHERE EmployeeID NOT IN(
SELECT EMP_ID FROM attendance_log
WHERE LOG_TIME BETWEEN '2013-04-15 00:00:00' AND '2013-04-15 23:59:59.999999'
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
This code is work to get the no IN and OUT for the particular date:
now i need a query to get only the employee with IN and OUT indicator:
SELECT emp.employeeid, emp.lastname, emp.firstname, emp.middlename, emp.department
FROM employees AS emp
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;
also a query which the employee has only IN or has only OUT.
Thank you so much...
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)