SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    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,015
    Mentioned
    53 Post(s)
    Tagged
    2 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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    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,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    I tried it and my sql was not responding.
    oh, that's too bad
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    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
    657
    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
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 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...

  8. #8
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    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...

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 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
  •