How to show NULL in fields where the condition is not satisfied in SQL

Asked

Viewed 37 times

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?

  • 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.

  • 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

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.