select distinct E.USERID as [رقم الموظف], U.User_FirstName+ ' ' + U.User_LastName as [اسم الموظف] , CONVERT(Date,LOCALTIMESTAMP) as [تاريخ العمل], (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 [وقت الدخول], (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 [وقت الخروج] from access_event_logs As E Inner join access_user as U on E.USERID = U.User_ID where Convert(date,LOCALTIMESTAMP) between '14/May/2014' And '14/May/2014'
select case when column1 is null then 'Absent' else column1 end from table1
ISNULL
COALESCE
AND ISNULL(MAXCE.EVENTID, 'Absent') ='Out'
AND COALESCE(MAXCE.EVENTID, 'Absent') ='Out'
select ISNULL(columnName,'What ever would you like to replace') from TableName
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)