Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    712
    Thanks
    165
    Thanked 0 Times in 0 Posts

    Select Employee with no attendance

    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
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Two ways:
    Code:
    SELECT E.* 
    FROM employees AS E LEFT JOIN attendance_log AS A
    ON ( E.employeeid = A.emp_id AND DATE(A.log_time) = '2013-5-2' )
    WHERE A.emp_id IS NULL
    AND E.sub = 'REG' 
    AND E.Department IN ('QA', 'Engineering', 'Assembly')
    That's the more SQL-ish way to do it. And probably performs better.

    But this might be easier for you to understand how it works:
    Code:
    SELECT * FROM employees
    WHERE sub = 'REG' 
    AND Department IN ('QA', 'Engineering', 'Assembly')
    AND employeeid NOT IN (
        SELECT emp_id FROM attendance_log 
        WHERE DATE(log_time) = '2013-5-2' )
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (05-15-2013)

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    712
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Hi,

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

    Code:
    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...

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Code:
    SELECT emp.employeeid, emp.lastname, emp.firstname, emp.middlename, emp.department 
    FROM employees AS emp
    LEFT JOIN attendance_log AS attIN
         ON (     attIN.emp_id = emp.employeeid
              AND DATE(att.log_time) = '2013-05-14'
              AND attIN.indicator = 'IN' )
    LEFT JOIN attendance_log AS attOUT
         ON (     attOUT.emp_id = emp.employeeid
              AND DATE(att.log_time) = '2013-05-14'
              AND attOUT.indicator = 'OUT' )
    WHERE emp.sub = 'REG' 
      AND emp.department IN ('Assembly', 'Fabrication', 'Compounding', 'Finishing', 'PET', 'Production', 'Squash', 'QA', 'ENGINEERING')
      AND attIN.emp_id IS NOT NULL
      AND attOUT.emp_id IS NOT NULL
    That query will find you all employees who have both IN and OUT on the given day.

    To find those with IN but no OUT, just change the last two lines to:
    Code:
      AND attIN.emp_id IS NOT NULL
      AND attOUT.emp_id IS NULL
    To find those with OUT but no IN just change the last two lines to:
    Code:
      AND attIN.emp_id IS NULL
      AND attOUT.emp_id IS NOT NULL
    BUT...
    But we could also create a UNIFIED report that showed *all* employess, and showed the IN and OUT for each.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •