0
I have this Input table (p):
EmployeeID EventId EntityId EnterDate
11111 100 1 2:54:11
11111 100 1 2:54:43
11111 100 1 2:56:03
11111 100 1 2:56:35
11111 100 1 2:58:51
And this Output table (q):
EmployeeID EventId EntityId ExitDate
11111 100 3 2:52:46
11111 100 3 2:53:29
11111 100 3 2:55:15
11111 100 3 2:57:33
11111 100 3 2:58:15
11111 100 3 6:26:08
With that code:
SELECT DISTINCT TOP (100) PERCENT u.EmployeeId, CAST(p.DeviceTS AS Date) AS Day, p.DeviceTS AS EnterDate, MIN(q.DeviceTS) AS ExitDate
FROM (SELECT a.DeviceTS, a.UserId
FROM dbo.DeviceLog AS a INNER JOIN
dbo.Users AS u ON a.UserId = u.Id
WHERE (a.EventId = 100) AND (a.EntityId IN (1, 2, 5, 6, 9, 10, 20, 25, 27))) AS p LEFT OUTER JOIN
(SELECT a.DeviceTS, a.UserId
FROM dbo.DeviceLog AS a INNER JOIN
dbo.Users AS u ON a.UserId = u.Id
WHERE (a.EventId = 100) AND (a.EntityId IN (3, 4, 7, 8, 11, 12, 21, 26, 28))) AS q ON p.UserId = q.UserId AND CAST(p.DeviceTS AS Date) = CAST(q.DeviceTS AS Date) AND
q.DeviceTS > p.DeviceTS INNER JOIN
dbo.Users AS u ON p.UserId = u.Id AND u.EmployeeId IS NOT NULL
GROUP BY u.EmployeeId, p.DeviceTS
ORDER BY u.EmployeeId, EnterDate, ExitDate
Returns this result:
EmployeeId EnterDate ExitDate
1 11111 2:54:11 2:55:15
2 11111 2:54:43 2:55:15
3 11111 2:56:03 2:57:33
4 11111 2:56:35 2:57:33
5 11111 2:58:51 6:26:08
I wanted to do so so that when there were two entries or two exits in a row it would represent the value as null as the following table:
EmployeeId EnterDate ExitDate
1 11111 NULL 2:52:46
2 11111 NULL 2:53:29
3 11111 2:54:11 NULL
4 11111 2:54:43 2:55:15
5 11111 2:56:03 NULL
6 11111 2:56:35 2:57:33
7 11111 NULL 2:58:15
8 11111 2:58:51 6:26:08
Could you do that? Note: I cannot use IF, CASE, etc.
I’m sorry, but I don’t understand why you’re a duplicate. Could you explain to me what was wrong or how I could use it to solve my problem?
– José Augusto
If you want results where there is no match on both sides, Join cannot be Inner. See graphs and link responses above. INNER does not return cases where a side is null.
– Bacco
The Inner is only used to catch the employeeid, in this, will not have null, for the comparison of Enterdate and Exitdate was used the Left Outer Join
– José Augusto