www.webdeveloper.com
Results 1 to 3 of 3

Thread: select employee where no attendance

  1. #1
    Join Date
    Oct 2011
    Posts
    350

    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.

    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. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,176
    Code:
    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'
    )
    (make sure there's an index on LOG_TIME so that the query is efficient on large data sets)
    "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

    eBookworm.us

  3. #3
    Join Date
    Oct 2011
    Posts
    350
    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...

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles