Hi Sharon27,

Thanks for the DDL and consumable sample data. I added a couple more punches for Alvin and Rebecca to the sample data to test:

INSERT INTO EMPTMS

(uidfirstname,uidlastname,reader,dtdate)

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

INSERT INTO EMPTMS

(uidfirstname,uidlastname,reader,dtdate)

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

INSERT INTO EMPTMS

(uidfirstname,uidlastname,reader,dtdate)

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

INSERT INTO EMPTMS

(uidfirstname,uidlastname,reader,dtdate)

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

It appears from the sample data that "IN" punches occur on reader 1 and "OUT" punches occur on reader 2. If this holds true in all cases, this gives you the result you want:

SELECT c1.uidfirstname, c1.uidlastname, c1.dtdate AS logDateTimeIn, c2.dtdate AS logDateTimeOut

FROM (SELECT uidfirstname, uidlastname, reader, dtdate, ROW_NUMBER() OVER (PARTITION BY uidfirstname, uidlastname ORDER BY dtdate, reader) as punch_number FROM dbo.EMPTMS ) c1

LEFT OUTER JOIN (SELECT uidfirstname, uidlastname, reader, dtdate, ROW_NUMBER() OVER (PARTITION BY uidfirstname, uidlastname ORDER BY dtdate, reader) as punch_number FROM dbo.EMPTMS ) c2

ON c1.uidfirstname = c2.uidfirstname

AND c1.punch_number + 1 = c2.punch_number

WHERE c1.reader = 1

If you're actually working in SQL Server 2005 or above (you posted in a forum for SQL Server 2000 and SQL Server 7), you can use a CTE to make your code a little neater:

WITH cte_punches AS (SELECT uidfirstname, uidlastname, reader, dtdate, ROW_NUMBER() OVER (PARTITION BY uidfirstname, uidlastname ORDER BY dtdate, reader) as punch_number FROM dbo.EMPTMS )

SELECT c1.uidfirstname, c1.uidlastname, c1.dtdate AS logDateTimeIn, c2.dtdate AS logDateTimeOut

FROM cte_punches c1

LEFT OUTER JOIN cte_punches c2

ON c1.uidfirstname = c2.uidfirstname

AND c1.punch_number + 1 = c2.punch_number

WHERE c1.reader = 1

This works in SQL 2005 and above, too:

WITH cte_punches AS (SELECT uidfirstname, uidlastname, reader, dtdate, ROW_NUMBER() OVER (PARTITION BY uidfirstname, uidlastname ORDER BY dtdate, reader) as punch_number FROM dbo.EMPTMS )

SELECT c1.uidfirstname, c1.uidlastname, c1.dtdate AS logDateTimeIn, c2.dtdate AS logDateTimeOut

FROM cte_punches c1

CROSS APPLY (SELECT c.dtdate FROM cte_punches c WHERE c1.uidfirstname = c.uidfirstname

AND c1.punch_number + 1 = c.punch_number) c2

WHERE c1.reader = 1

Performance may vary among the different versions, so test them all if necessary.