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:
x
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.
Advertisement
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)