--------------------------------------------------------------------------------

-- Modified sample dataset

--------------------------------------------------------------------------------

DROP TABLE EMPTMS

CREATE TABLE EMPTMS

(Uidfirstname varchar (50),

Uidlastname varchar(50),

Reader int,

dtdate datetime);

SET DATEFORMAT YMD

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 '),

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

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

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

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

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

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

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

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

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

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

('002','Rebecca','1','2013-09-26 22:05:000 '),

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

('002','Rebecca','2','2013-09-27 7:35:000 '),

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

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

--------------------------------------------------------------------------------

-- Proposed solution, v01

--------------------------------------------------------------------------------

WITH FiddledData AS (

SELECT e.uidfirstname, e.uidlastname, e.reader, e.dtdate,

gn = reader - ROW_NUMBER() OVER(PARTITION BY e.uidfirstname, e.uidlastname ORDER BY z.NewDate, y.marker)

-- z.NewDate, y.Marker --

FROM EMPTMS e

OUTER APPLY ( -- any other clock events within 2 hours? Use to modify grouping

SELECT TOP 1 i.dtdate

FROM EMPTMS i

WHERE i.Uidfirstname = e.Uidfirstname

AND i.Uidlastname = e.Uidlastname

AND i.dtdate <> e.dtdate

AND ABS(DATEDIFF(HOUR,i.dtdate,e.dtdate)) < 2 -- arbitrary figure

) x

CROSS APPLY (

SELECT Marker = CASE

WHEN x.dtDate IS NOT NULL THEN

CASE WHEN x.dtDate < e.dtDate THEN 1 ELSE 2 END

ELSE NULL END

) y

CROSS APPLY (

SELECT NewDate = CASE WHEN y.Marker = e.Reader THEN x.dtDate ELSE e.dtDate END

) z

),

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);