select rows with events related with another events in the same query column

I need to select rows with EventTypeID = 19 which does not have related EventtypeID = 21 LoggedOn exactly 4 minutes earlier for the same EmployeeID. Here’s the query bellow and some raw output:

SELECT * FROM
    (
        SELECT rcp..EventLogEntries.EmployeeID, rcp..EventLogEntries.EventTypeID, rcp..EventLogEntries.TerminalID, rcp..EventLogEntries.LoggedOn
        FROM rcp..EventLogEntries
        WHERE rcp..EventLogEntries.terminalid = 3
        UNION
        SELECT viso..AccessUserPersons.UserExternalIdentifier, rcp..EventTypes.ID, rcp..Terminals.ID, viso..EventLogEntries.LoggedOn
        FROM viso..EventLogEntries, viso..AccessUserPersons, rcp..Terminals, rcp..EventTypes
        WHERE viso..EventLogEntries.LocationID = 10
        AND viso..EventLogEntries.EventCode = 615
        AND rcp..EventTypes.Code = 36
        AND viso..EventLogEntries.PersonID = viso..AccessUserPersons.ID
        AND viso..EventLogEntries.locationID = rcp..Terminals.TerminalTAID
    ) results
ORDER BY LoggedOn


EmployeeID  EventTypeID TerminalID  LoggedOn
273         19          3           2018-12-04 12:31:23.000
273         21          3           2018-12-04 12:34:18.000
483         19          3           2018-12-04 12:40:10.000
268         19          3           2018-12-04 13:19:23.000
273         21          3           2018-12-04 13:28:00.000
273         19          3           2018-12-04 13:32:00.000
459         19          3           2018-12-04 15:01:04.000

What I need to achieve is:

EmployeeID  EventTypeID TerminalID  LoggedOn
273         19          3           2018-12-04 12:31:23.000
483         19          3           2018-12-04 12:30:10.000
268         19          3           2018-12-04 13:19:23.000
459         19          3           2018-12-04 15:01:04.000

TerminalID column value is always 3 in that scenario and it’s not related with any query condition, but must be in the output for syntax requirement in the futher processing.

Answer

The bad practice to join tables using conditions in WHERE. The block WHERE need to use to filter first of all. And aliases help to make code shorter.

SELECT * FROM
    (
        SELECT EmployeeID, EventTypeID, TerminalID, LoggedOn
        FROM rcp..EventLogEntries
        WHERE terminalid = 3
        UNION
        SELECT p.UserExternalIdentifier, et.ID, t.ID, el.LoggedOn
        FROM viso..EventLogEntries el
        JOIN viso..AccessUserPersons p ON el.PersonID = p.ID
        JOIN rcp..Terminals t ON el.locationID = t.TerminalTAID
        JOIN rcp..EventTypes et ON --!!! no any condition here
        WHERE el.LocationID = 10
          AND el.EventCode = 615
          AND et.Code = 36
    ) results
ORDER BY LoggedOn

Try to use the following:

WITH cteData AS
    (
        SELECT EmployeeID, EventTypeID, TerminalID, LoggedOn
        FROM rcp..EventLogEntries
        WHERE terminalid = 3
        UNION
        SELECT p.UserExternalIdentifier, et.ID, t.ID, el.LoggedOn
        FROM viso..EventLogEntries el
        JOIN viso..AccessUserPersons p ON el.PersonID = p.ID
        JOIN rcp..Terminals t ON el.locationID = t.TerminalTAID
        JOIN rcp..EventTypes et ON --!!! no any condition here
        WHERE el.LocationID = 10
          AND el.EventCode = 615
          AND et.Code = 36
    )
SELECT q19.*
FROM
  (
    SELECT *
    FROM cteData
    WHERE EventTypeID=19
  ) q19
LEFT JOIN
  (
    SELECT *
    FROM cteData
    WHERE EventTypeID=21
  ) q21
ON q19.EmployeeID=q21.EmployeeID
WHERE (DATEDIFF(MINUTE,q19.LoggedOn,q21.LoggedOn)>4 OR q21.LoggedOn IS NULL)

If there don’t need any conditions you can use CROSS JOIN.

I got your result using your data:

WITH cteData AS(
  SELECT *
  FROM (VALUES
        (273,19,3,CAST('2018-12-04 12:31:23.000' AS datetime)),
        (273,21,3,CAST('2018-12-04 12:34:18.000' AS datetime)),
        (483,19,3,CAST('2018-12-04 12:40:10.000' AS datetime)),
        (268,19,3,CAST('2018-12-04 13:19:23.000' AS datetime)),
        (273,21,3,CAST('2018-12-04 13:28:00.000' AS datetime)),
        (273,19,3,CAST('2018-12-04 13:32:00.000' AS datetime)),
        (459,19,3,CAST('2018-12-04 15:01:04.000' AS datetime))
    )v(EmployeeID,EventTypeID,TerminalID,LoggedOn)
)
SELECT q19.*
FROM
  (
    SELECT *
    FROM cteData
    WHERE EventTypeID=19
  ) q19
LEFT JOIN
  (
    SELECT *
    FROM cteData
    WHERE EventTypeID=21
  ) q21
ON q19.EmployeeID=q21.EmployeeID
WHERE (DATEDIFF(MINUTE,q19.LoggedOn,q21.LoggedOn)>4 OR q21.LoggedOn IS NULL)