Try this:
DECLARE @tmp TABLE(Emp_Name VARCHAR(30), Emp_Time DATETIME, Emp_Type VARCHAR(3))
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-01 08:30:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-01 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-01 17:25:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-01 17:35:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-02 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-02 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-02 17:45:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Mike', '2013-04-02 17:55:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-01 08:30:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-01 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-01 17:25:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-01 17:35:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-02 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-02 08:35:48', 'In')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-02 17:45:32', 'Out')
INSERT INTO @tmp (Emp_Name, Emp_Time, Emp_Type)
VALUES('Josh', '2013-04-02 17:55:32', 'Out')
SELECT t3.RowNo, t3.Emp_Name, t3.DateInOut, t3.Time_In, t4.Time_Out
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY Emp_Name, Time_In) AS RowNo, t1.*
FROM(
SELECT Emp_Name, CONVERT(VARCHAR(10), Emp_Time,121) AS DateInOut, MIN(Emp_Time) AS Time_In
FROM @tmp
WHERE Emp_Type = 'In'
GROUP BY Emp_Name, CONVERT(VARCHAR(10), Emp_Time,121)
) AS t1) AS t3 INNER JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY Emp_Name, Time_Out) AS RowNo, t2.*
FROM(
SELECT Emp_Name, CONVERT(VARCHAR(10), Emp_Time,121) AS DateInOut, MAX(Emp_Time) AS Time_Out
FROM @tmp
WHERE Emp_Type = 'Out'
GROUP BY Emp_Name, CONVERT(VARCHAR(10), Emp_Time,121)
) AS t2) AS t4 ON t3.RowNo = t4.RowNo
Returned values:
R. Emp_N. DateInOut Time_In Time_Out
1 Josh 2013-04-01 2013-04-01 08:30:48.000 2013-04-01 17:35:32.000
2 Josh 2013-04-02 2013-04-02 08:35:48.000 2013-04-02 17:55:32.000
3 Mike 2013-04-01 2013-04-01 08:30:48.000 2013-04-01 17:35:32.000
4 Mike 2013-04-02 2013-04-02 08:35:48.000 2013-04-02 17:55:32.000
You can extract only time from
Time_In
and
Time_Out
. To do it, use
CAST or CONVERT function[
^].