Hi go through the following code block
DECLARE @attn TABLE (StaffNo INT, ADate VARCHAR(20), ATime VARCHAR(20));
INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 09.10);
INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 10.03);
INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 10.40);
INSERT INTO @attn (StaffNo, ADate, ATime) VALUES ('12345', '12-02-2013', 17.30);
WITH tmp AS (
SELECT ROW_NUMBER() OVER(ORDER BY StaffNo,ADate) AS RowNum,* FROM @attn
)
select tmp1.StaffNo,tmp1.ADate,tmp1.ATime InTime,tmp2.ATime OutTime
FROM tmp as tmp1,tmp as tmp2 WHERE tmp1.RowNum = tmp2.RowNum - 1
AND tmp2.RowNum % 2 = 0 AND tmp1.StaffNo = tmp2.StaffNo
Thank you.