Last post Feb 28, 2011 07:09 PM by iLes
Feb 28, 2011 01:13 AM|iLes|LINK
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!
Feb 28, 2011 02:37 AM|kedarrkulkarni|LINK
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
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...
Feb 28, 2011 02:48 AM|krisrajz|LINK
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)
Feb 28, 2011 07:09 PM|iLes|LINK