;WITH InOut (empId, EntryTime, ExitTime,POINTID,EVENTID,Belongs_to) as
(SELECT emp_reader_id ,
a1.DT ,
( SELECT MIN(a2.DT)
FROM trnevents a2
WHERE a1.emp_reader_id = a2.emp_reader_id
AND a1.DT < a2.DT
),a1.DeviceSerialNumber,EVENTID,Belongs_to
FROM trnevents a1
)
SELECT empId ,
EntryTime ,
ExitTime,EVENTID, Belongs_to
,CONVERT( CHAR(11), '2017/12/30', 103 ) as StartDate
,CONVERT( CHAR(11), '2018/01/15', 103 ) as ToDate
,case when convert(time,dateadd(ms,DATEDIFF(SECOND, EntryTime, ExitTime)*1000,0),114) is not null
then convert(time,dateadd(ms,DATEDIFF(SECOND, EntryTime, ExitTime)*1000,0),114) else '00:00' end as Worked FROM InOut A
