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
How to Ask Questions the Smart Way
(not affiliated with this site, but well worth reading)
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)