This is a follow up to my previous question: https://dba.stackexchange.com/q/127715/1192 I am a beginner in SQL. I tried this query and CTE, but I cannot get my desired output: SELECT UserId, Convert(varchar(10),LogDate,120) as [Date], Min(CASE WHEN (C1 = 'out') THEN LogDate END) AS [Login], MAX(CASE WHEN (C1 = 'in') THEN LogDate END) AS [Logout] FROM Employee GROUP BY UserId,Convert(varchar(10),LogDate,120) ORDER BY UserId <!-- language-all: lang-none --> ###Shift Table UserID ShiftName Start End --------------------------------------------------- 1019 Night Shift-1 18:30 03:30 ###Employee Table UserID LogDate C1 --------------------------------------------------- 1019 2016-03-01 05:17:03.000 Out 1019 2016-03-01 18:41:14.000 In 1019 2016-03-01 22:06:24.000 Out 1019 2016-03-01 22:34:03.000 In 1019 2016-03-02 01:32:33.000 Out 1019 2016-03-02 01:38:03.000 In 1019 2016-03-02 05:32:33.000 Out ###Desired output Table1: UserId Date LogIN LogOUT ------------------------------------------------------------------- 1019 2016-03-01 2016-03-01 18:41:14 2016-03-01 22:06:24 1019 2016-03-01 2016-03-01 22:34:03 2016-03-02 01:32:33 1019 2016-03-01 2016-03-02 01:38:03 2016-03-02 05:32:33 ###Desired output Table2: UserId Date FirstIN LastOUT Working Hours --------------------------------------------------------------------------------------- 1019 2016-03-01 2016-03-01 18:41:14 2016-03-02 05:32:33 ? <!-- language-all: lang-sql --> Additional notes: * There may be multiple IN and OUT for each employee. * The shift times are 18:30 and 03:30. * Employees may come maximum 2 hours earlier or leave 2 hours later.