locked
Complicated Select Query RRS feed

  • Question

  • User-917082914 posted

    Hey Everyone,

    So I am kinda stuck here and need some help from the pros with a SQL Select statement.

    Some background first, I have two tables, one with users the others with hours (Clock in, Clock Out, Break In, Break Out, Day)

    I need to be able to execute a select query that returns everyone who doesnt have a record for a certain day. For instance if I have 20 users, and I want to return only the users that don't have a record in the hours table for Feb 1st 2011. 

     

    I was bascially thing like, Select FirstName, LastName, Day. Where Day is not equal to certain date. But that will just return every other day.

    I need something like, select everyone, and do not include people who have a record for that certain day.

     

    Anybody have any ideas?

    Thanks in advanced!

    Monday, February 28, 2011 1:13 AM

Answers

  • User-917082914 posted
    Thanks both for your answers, I used Raj's just changed it a bit. This is what I used to accomplish the task for anyone looking for the same result in the furture. SELECT FirstName + ' ' + LastName AS Name, UserID FROM UserList WHERE (UserID NOT IN (SELECT UserID FROM TimeTable WHERE (Day = @Day)))
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 28, 2011 7:09 PM

All replies

  • User551462331 posted

    ok... considering username/userid is common field in both the table

    following is the query... let me know if it dosent work (or i not uderstood the rquirement clearly..)

    select * from user_table user
    left join (select * from in_out_time_table where date= <enter desired date>) time
    on user.userid=time.userid
    where time.userid is null

    considering user table name is user_table and table to record time is in_out_time_table

    u can have user table left join time table with filtered values for perticular date

    if user ABC dont have time record in second table, in left join only username will be returned with time record as null, then u can have condition that to return all records where second table have returned null

    hope this helps...

    Monday, February 28, 2011 2:37 AM
  • User-1129879462 posted

    declare @LeaveOn Date='2011-Feb-3'

    select FirstName,LastName,'LeaveOn'=@LeaveOn from tblUsers

    where UserID not in

    (select UserID from tblHours where DATEDIFF(D,@LeaveOn,[Day])=0)

    Monday, February 28, 2011 2:48 AM
  • User-917082914 posted
    Thanks both for your answers, I used Raj's just changed it a bit. This is what I used to accomplish the task for anyone looking for the same result in the furture. SELECT FirstName + ' ' + LastName AS Name, UserID FROM UserList WHERE (UserID NOT IN (SELECT UserID FROM TimeTable WHERE (Day = @Day)))
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 28, 2011 7:09 PM