select USER_ID, User_FirstName+ ' ' + User_LastName as [Last Name] , NULL as InTime, NULL as OutTime FROM access_user WHERE user_automatch='1' and user_id not in --not exists " (select distinct e.userid as [user_id] from access_event_logs As E where Convert(date,LOCALTIMESTAMP) between '03-24-2014' and '03-24-2014' and (EVENTID ='In' or EVENTID='Out')) Union select distinct E.USERID, U.User_FirstName+ ' ' + U.User_LastName as [Last name], (select min(Convert(TIME,LOCALTIMESTAMP)) from access_event_logs As MINCE where CAST(MINCE.LOCALTIMESTAMP as DATE)=CAST(E.LOCALTIMESTAMP As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=E.USERID) as InTime, (select max(Convert(TIME,LOCALTIMESTAMP)) from access_event_logs As MAXCE where CAST(MAXCE.LOCALTIMESTAMP as DATE)=CAST(E.LOCALTIMESTAMP As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=E.USERID) as OutTime from access_event_logs As E Inner join access_user as U on E.USERID = U.User_ID WHERE Convert(date,LOCALTIMESTAMP) between '03-24-2014' and '03-24-2014' Order by User_ID
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)