SELECT ID
,[Date]
, MIN(CASE WHEN TimeIn BETWEEN '08:00:00' AND '12:00:00' THEN TimeIn END) Shift1In
, MAX(CASE WHEN TimeOut BETWEEN '08:00:00' AND '12:00:00' THEN [TimeOut] END) Shift1Out
, MIN(CASE WHEN TimeIn BETWEEN '12:00:00' AND '23:59:59' THEN TimeIn END) Shift2In
, MAX(CASE WHEN TimeOut BETWEEN '12:00:00' AND '23:59:59' THEN [TimeOut] END) Shift2Out
FROM @Shifts
GROUP BY ID, [Date]

DECLARE @Shifts TABLE(ID INT, [Date] DATE, TimeIn TIME)
insert into @Shifts
SELECT 123, '15/04/2013', '08:00:00' union all
SELECT 123, '15/04/2013', '08:10:00' union all 
SELECT 123, '15/04/2013', '10:00:00' union all 
SELECT 123, '15/04/2013', '12:00:00' union all 
SELECT 123, '15/04/2013', '16:30:00' union all 
SELECT 123, '15/04/2013', '17:00:00' union all 
SELECT 123, '15/04/2013', '18:00:00' union all 
SELECT 123, '15/04/2013', '20:00:00' union all
SELECT 123, '15/04/2013', '22:30:00'
/*What will be if the table in this format and there are two shift
Shift 1=9:00-12:00
SHift 2=16:30-22:30
How do i tranfer the data from the above table in format given below.
ID Date IN-SHIFT1 OUT-SHIFT1 IN-SHIFT2 OUT-SHIFT2
123 15/04/2013 8:00 12:00 16:30 22:30 */
SELECT ID
,[Date]
, MIN(CASE WHEN TimeIn <= '12:00:00' THEN TimeIn END) Shift1In
, MAX(CASE WHEN TimeIn <= '12:00:00' THEN TimeIn END) Shift1Out
, MIN(CASE WHEN TimeIn BETWEEN '16:30:00' AND '22:30:00' THEN TimeIn END) Shift2In
, MAX(CASE WHEN TimeIn BETWEEN '16:30:00' AND '22:30:00' THEN TimeIn END) Shift2Out
FROM @Shifts
GROUP BY ID, [Date]

SELECT ID
,[Date]
, MIN(CASE WHEN TimeIn <= '12:00:00' THEN TimeIn END) Shift1In
, MAX(CASE WHEN TimeOut <= '12:00:00' THEN TimeOut END) Shift1Out
, MIN(CASE WHEN TimeIn > '12:00:00' THEN TimeIn END) Shift2In
, MAX(CASE WHEN TimeOut > '12:00:00' THEN TimeOut END) Shift2Out
FROM @Shifts
GROUP BY ID, [Date]