I have an audit table in SQL Server which stores data like this for a single case:
Example 1
ID CREATED MESSAGE MEMO USER
------------------------------------------------------------------------------
A-123 08/02/2022 12:00:00 Generic Message 1 NULL System
A-123 08/02/2022 12:05:30 Generic Message 2 NULL System
A-123 08/02/2022 12:45:01 Reassigned to XYZ NULL System
A-123 08/02/2022 14:59:59 NULL Resolved User XYZ
A-123 08/02/2022 18:05:05 Reassigned to XYZ NULL System
Example 2
ID CREATED MESSAGE MEMO USER
--------------------------------------------------------------------------------
A-987 07/02/2022 12:00:00 Generic Message 1 NULL System
A-987 07/02/2022 12:05:30 Generic Message 2 NULL System
A-987 07/02/2022 12:45:01 Generic Message 3 NULL System
A-987 07/02/2022 14:59:59 NULL Resolved User XYZ
A-987 07/02/2022 18:05:05 Reassigned to XYZ NULL System
Using SQL I want to only select the ID value in these circumstances:
When there is a MESSAGE value of "Reassigned to XYZ" with a USER value of "System"
When point 1. occurs at an earlier time than the record that contains a MEMO value of "Resolved".
In the 2 examples above, example 1 would output ID A-123 because the 2 criteria have met, but example 2 wouldn't output anything because the "Reassigned to XYZ" MESSAGE value is after the timestamp of the "Resolved" MEMO entry.
I think I need to rank() and then do something but I can't work out what.
I don't see a need for a window function here. There are several ways to address this: here are but a few UNTESTED ways.
USING an Exists to limit data: Get all XYZ, find any occurrence of a XYZ create date before a resolved data. and return that ID multiple ID's would return if multiple "reassigned to XYZ' Id's exist.
SELECT ID
FROM TABLE
WHERE message = 'Reassigned to XYZ'
AND exists (SELECT 1
FROM TABLE
WHERE memo = 'Resolved'
AND A.ID = B.ID
AND A.Created < B.Created)
Using CTE's and an join. Similar to exists; show any XYZID whose created data is < the resolve'd created dat a for a same ID. Could have multiple rows returned
WITH XYZ as (SELECT ID, Created FROM TABLE WHERE message = 'Reassigned to XYZ'),
Resolved as (SELECT ID, Created FROM TABLE WHERE memo = 'Resolved')
SELECT XYZ.ID
FROM XYZ
INNER JOIN Resolved
on XYZ.ID = Resolve.ID
AND XYZ.Created < Resolved.Created
You could use an aggregate as well I suppose and case expression
This assumes you could have multiple Reassigned to XYZ or resolved, and all it takes is for 1 XYZ to be before resolved for the ID to show up. only 1 ID would ever be returned if condition is met.
SELECT ID,
MIN(CASE when Message = 'Reassigned to XYZ' then create end) as MinZYZ,
MIN(Case when memo = 'Resolved' then create end) MinMemo
FROM Table
GROUP BY ID
HAVING MINXYZ < MinMemo
I suppose we could do the min/case expression in the having to eliminate the values from the select if needed... simply substitute the expression for the aliased column name used
you could use cross apply cross apply is a table value function which allows the sub query to run once per record in the left table and operates like an inner join in that if no records are found the record is eliminated. So only records which are XYZ and have a resolved created data greater than the XYZ record will be present.
SELECT Distinct XYZ.ID
FROM Table XYZ
CROSS APPLY (SELECT TOP 1 ID, Created FROM TABLE WHERE memo = 'Resolved' ORDER BY Created ASC) Resolved
on XYZ.ID = Resolve.ID
AND XYZ.Created < Resolved.Created
NOTES: