Perfect. Thanks for posting that file.

Ignoring the TimeN and InOut columns that you made, here's the code to pivot (using a CROSSTAB) the pairs of times, calculate the differences between each time pair, and format the results.

WITH cteEnumerate AS

(

SELECT PayPunchID, PayrollID, FirstName, LastName, ActualTime, RoundedTime, Date,

TimeN = ROW_NUMBER() OVER (PARTITION BY PayrollID,Date ORDER BY ActualTime)-1

FROM #Temp

),

cteCrossTab AS

(

SELECT PayPunchID, PayrollID, FirstName, LastName, Date,

ActualTimeIn = MAX(CASE WHEN TimeN %2 = 0 THEN ActualTime ELSE 0 END),

ActualTimeOut = MAX(CASE WHEN TimeN %2 = 1 THEN ActualTime ELSE 0 END),

RoundedTimeIn = MAX(CASE WHEN TimeN %2 = 0 THEN RoundedTime ELSE 0 END),

RoundedTimeOut = MAX(CASE WHEN TimeN %2 = 1 THEN RoundedTime ELSE 0 END)

FROM cteEnumerate

GROUP BY PayPunchID, PayrollID, FirstName, LastName, Date, TimeN/2

)

SELECT PayPunchID, PayrollID, FirstName, LastName,

Date = CONVERT(CHAR( 7),Date ,120),

ActualTimeIn = CONVERT(CHAR( 5),ActualTimeIn ,108),

ActualTimeOut = CONVERT(CHAR( 5),ActualTimeOut ,108),

ActualDuration = CONVERT(CHAR( 5),ActualTimeOut -ActualTimeIn ,108),

RoundedTimeIn = CONVERT(CHAR( 5),RoundedTimeIn ,108),

RoundedTimeOut = CONVERT(CHAR( 5),RoundedTimeOut,108),

RoundedDuration = CONVERT(CHAR( 5),RoundedTimeOut-RoundedTimeIn,108)

FROM cteCrossTab

;

That returns the following...

PayPunchID PayrollID FirstName LastName Date ActualTimeIn ActualTimeOut ActualDuration RoundedTimeIn RoundedTimeOut RoundedDuration

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

8412 73 Jone Daua 2014-03 10:56 13:57 03:01 11:00 13:45 02:45

8412 73 Jone Daua 2014-03 14:39 20:46 06:07 14:45 20:45 06:00

8412 73 Jone Daua 2014-03 07:20 10:58 03:38 07:30 10:45 03:15

8412 73 Jone Daua 2014-03 11:19 16:13 04:54 11:30 16:00 04:30

8412 73 Jone Daua 2014-03 09:51 13:33 03:42 10:00 13:30 03:30

8412 73 Jone Daua 2014-03 14:07 19:53 05:46 14:15 19:45 05:30

8412 73 Jone Daua 2014-03 09:48 13:51 04:03 10:00 13:45 03:45

8412 73 Jone Daua 2014-03 14:30 19:54 05:24 14:45 19:45 05:00

8412 73 Jone Daua 2014-03 06:48 10:21 03:33 07:00 10:15 03:15

8412 73 Jone Daua 2014-03 10:38 13:39 03:01 10:45 13:30 02:45

8412 73 Jone Daua 2014-03 14:09 17:00 02:51 14:15 17:00 02:45

8412 73 Jone Daua 2014-03 10:38 14:23 03:45 10:45 14:15 03:30

8412 73 Jone Daua 2014-03 14:54 20:20 05:26 15:00 20:15 05:15

8412 73 Jone Daua 2014-03 06:47 10:10 03:23 07:00 10:00 03:00

8412 73 Jone Daua 2014-03 10:35 13:27 02:52 10:45 13:15 02:30

8412 73 Jone Daua 2014-03 14:01 20:40 06:39 14:15 20:30 06:15