mdarr mdarr - 7 months ago 33
SQL Question

SQL Server 2012 SELECT max date based on multiple conditions

I have a table with approximately 8 million rows. The rows contain an ID, a date, and an event code. I would like the select all the ID's and dates for which the event code is equal to 1 and there has been an event code equal to 2 at some time in the past.

For example, my table looks like this:

ID Date Code
----------------------
1 4/16/2016 6
1 4/10/2016 1
1 3/1/2016 13
1 1/26/2016 2
2 5/2/2016 8
2 3/14/2016 1
2 1/13/2016 14


I would want ID = 1 and Date = 4/10/2016 returned but I would not want anything returned with ID=2 because ID=2 never had an event code equal to 2.

How should I write my
SELECT
statement to get these results?

Answer

If you only want to select the max date for each ID:

WITH Cte AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date DESC)
    FROM tbl
    WHERE Code = 1
)
SELECT
    ID, Date, Code
FROM Cte c
WHERE
    rn = 1
    AND EXISTS(
        SELECT 1
        FROM tbl t
        WHERE
            t.ID = c.ID
            AND t.Date < c.Date
            AND t.Code = 2
    )
;

ONLINE DEMO


Using MAX, GROUP BY, and HAVING:

SELECT
    ID, Date = MAX(Date)
FROM tbl  t1
WHERE Code = 1
GROUP BY t1.ID
HAVING MAX(Date) > (SELECT Date FROM tbl t2 WHERE t2.ID = t1.ID AND t2.Code = 2)

ONLINE DEMO