I want to group multiple rows into one based on the value of a column.
Given this data:
SELECT EmployeeID, RoomId , [Event], EventDate
FROM TimeLog
WHERE EmployeeID = '107733'
AND EventDate BETWEEN '2020/02/26 00:00:00' AND '2020/02/26 23:59:59'
ORDER BY EventDate
| EmployeeID | RoomID | Event | EventDate |
|---|---|---|---|
| 107733 | 05-27F | Login | 2020-02-26 07:02:00 |
| 107733 | 05-27F | Logout | 2020-02-26 08:38:00 |
| 107733 | 05-25F | Login | 2020-02-26 08:39:00 |
| 107733 | 05-25F | Logout | 2020-02-26 08:51:00 |
| 107733 | 05-27F | Login | 2020-02-26 08:52:00 |
| 107733 | 05-27F | Logout | 2020-02-26 12:00:00 |
So based on the value of the Event column which contains 2 possible values - Login and Logout, I want to combine 2 rows into one so that the result would be something like this:
| EmployeeID | RoomID | Login | Logout |
|---|---|---|---|
| 107733 | 05-27F | 2020-02-26 07:02:00 | 2020-02-26 08:38:00 |
| 107733 | 05-25F | 2020-02-26 08:39:00 | 2020-02-26 08:51:00 |
| 107733 | 05-27F | 2020-02-26 08:52:00 | 2020-02-26 12:00:00 |
Additional Requirement (if possible):
- It should be sorted chronologically
- (EDGE CASE) In case "Login" is missing or has no value, it should get the value of the Logout, same if "Logout" has no value - it should get the value of the "Login" field. So Login and Logout will have the same value if one is missing.
| EmployeeID | RoomID | Event | EventDate |
|---|---|---|---|
| 107733 | 05-27F | Login | 2020-02-26 07:02:00 |
| 107733 | 05-25F | Logout | 2020-02-26 08:38:00 |
Note that in this case, this happens in different "Room". On 05-27F there's no Logout, while in 05-25F there's no Login. Expected result would be:
| EmployeeID | RoomID | Login | Logout |
|---|---|---|---|
| 107733 | 05-27F | 2020-02-26 07:02:00 | 2020-02-26 07:02:00 |
| 107733 | 05-25F | 2020-02-26 08:38:00 | 2020-02-26 08:38:00 |
My attempt to solve this problem:
SELECT
EmployeeID,
RoomID,
'Login' = (SELECT TOP 1 EventDate
FROM TimeLog li
WHERE li.EmployeeID = tl.EmployeeID
AND li.RoomID = tl.RoomID
AND li.[Event] = 1),
'Logout' = (SELECT TOP 1 EventDate
FROM TimeLog lo
WHERE lo.EmployeeID = tl.EmployeeID
AND lo.RoomID = tl.RoomID
AND lo.[Event] = 2)
FROM
TimeLog tl
WHERE
tl.EmployeeID = '107733'
AND tl.EventDate BETWEEN '2020/02/26 00:00:00' AND '2020/02/26 23:59:59'