SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    do you remember the LEFT OUTER JOIN with IS NULL check?
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your suggested query. I tried it and my sql was not responding.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    I tried it and my sql was not responding.
    oh, that's too bad
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It ends as not responding because i think the sql cannot load the query

    Thank you.

  6. #6
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried this query:

    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 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

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    and sad to say all data from employees where department is Assembly was displayed even they had an attendance.
    but none of them had an attendance on 2013-05-14, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,491
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Even if they had an attendance on 2013-05-14 ?

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

  9. #9
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,491
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    Thank you so much...
    You're welcome


Bookmarks

Posting Permissions

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