CREATE TABLE EMPTMS

(Uidfirstname varchar (50),

Uidlastname varchar(50),

Reader int,

dtdate datetime);

INSERT INTO EMPTMS (uidfirstname, uidlastname, reader, dtdate) VALUES

('001','Alvin','1','2013-09-20 6:30:000 '),

('001','Alvin','2','2013-09-20 17:50:000 '),

('002','Rebecca','1','2013-09-20 22:10:000 '),

('003','Aliyah','1','2013-09-20 9:08:000 '),

('002','Rebecca','2','2013-09-21 7:30:000 '),

('001','Alvin','1','2013-09-21 6:30:000 '),

('001','Alvin','2','2013-09-21 17:50:000 '),

('002','Rebecca','1','2013-09-21 22:10:000 '),

('002','Rebecca','2','2013-09-22 7:30:000 '),

('002','Rebecca','2','2013-09-24 7:30:000 '),

('002','Rebecca','1','2013-09-25 22:10:000 '),

('002','Rebecca','2','2013-09-26 7:30:000 '),

('002','Rebecca','1','2013-09-28 22:10:000 ');

WITH FiddledData AS (

SELECT uidfirstname, uidlastname, reader, dtdate,

gn = reader - ROW_NUMBER() OVER(PARTITION BY uidfirstname,uidlastname ORDER BY dtDate)

FROM EMPTMS

),

ins AS (SELECT * FROM FiddledData WHERE reader = 1),

outs AS (SELECT * FROM FiddledData WHERE reader = 2)

SELECT

uidfirstname = ISNULL(i.uidfirstname, o.uidfirstname),

uidlastname = ISNULL(i.uidlastname, o.uidlastname),

DateIn = i.dtdate,

DateOut = o.dtdate,

MinutesWorked = DATEDIFF(minute,i.dtdate,o.dtdate)

FROM ins i

FULL OUTER JOIN outs o

ON o.Uidfirstname = i.Uidfirstname

AND o.Uidlastname = i.Uidlastname

AND o.gn = i.gn

ORDER BY ISNULL(i.uidfirstname, o.uidfirstname),

ISNULL(i.uidlastname, o.uidlastname),

ISNULL(i.dtdate,o.dtdate)