I am a beginner of SQL. I tried this Query and CTE But I cannot get my desire 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
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-01 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-01 01:38:03 2016-03-02 05:32:33
Desired output Table2:
UserId Date FirstIN LastOUT
-------------------------------------------------------------------
1019 2016-03-01 2016-03-01 18:41:14 2016-03-02 05:32:33