For each unique record ID, return the most recent record of type Y iff there is a more recent record of type X

Question

For each unique record ID, return the most recent record of type Y iff there is a more recent record of type X

To make explaining easier I will put the records sorted by EventDate descending and look only at specific record ID's. (Most recent at the top.)

Case 1

ID	EventDate	Type
1	Some Dates	Otherstuff (multiple records)
1	July 29	X
1	Feb 23	Y
1	Jan 3	Y
1	Some Dates	Otherstuff (multiple records)

Return record from Feb 23 of Type Y (Feb 23 is a closer date to the Jan 1 date of record with type X)

Case 2

ID	EventDate	Type
2	Some Dates	Otherstuff (multiple records)
2	Nov 2	X
2	Oct 31	Y
2	Some Dates	Otherstuff
2	July 2	X
2	Feb 23	Y
2	Jan 5	Y
2	Some Dates	Otherstuff

Return records from Feb 23 of type Y and Oct 31 of Type Y. These are the records that are the closest to the type X records in terms of date respectively. (Feb 23 Type y is closest to July 2 of type X and Oct 31 type Y is closest to Nov 2 type X)

Case 3

ID	EventDate	Type
3	Some Dates	Otherstuff (multiple records)
3	July 2	X
3	Feb 23	Y
3	Some Dates	Otherstuff
3	Jan 5	X
3	Some Dates	Otherstuff

Return Feb 23 of type Y record

Case 4

ID	EventDate	Type
4	Some Dates	Otherstuff (multiple records)
4	Oct 15	Y
4	July 2	X
5	Feb 23	X
5	Some Dates	Otherstuff
5	Jan 5	Y
5	Jan 1	Y
5	Some Dates	Otherstuff

Return ONLY the Jan 5th of type Y record. It is the closest to record of type X in terms of dates that has happened before the type X

SELECT
    *
FROM
    (
        SELECT
            *,
            ROW_NUMBER() OVER ( PARTITION BY A.ID ORDER BY EventDate DESC ) AS pc

        FROM
            SOMETABLE AS "A"
            INNER JOIN
            (
                SELECT
                    ID AS 'BID',
                    MIN(EventDate) AS 'OldestDate'
                FROM
                    SOMETABLE
                WHERE
                    TYPE = 'X' 
                GROUP BY
                    ID
            ) AS "B" ON A.ID = B.BID

    WHERE
        EventDate < OldestDate
        AND
        Type = 'Y'

    ) AS "FINAL"

This fails in cases where there are multiple records of type Y that need to be pulled, as it 'filters out' any records newer than the OLDEST instance of type X.

Answer 1

This query takes the most recent Y value for a given X value if it exists.

SELECT 
    * 
    ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY XDateTime ASC) AS 'Degree'
FROM
    (SELECT 
        *
        ,ROW_NUMBER() OVER (PARTITION BY YDateTime ORDER BY XDateTime ASC) AS 'dc'
    FROM
        (SELECT
            ID
            ,EventDateTime AS 'YDateTime'
            ,B.XDateTime
            ,DATEDIFF(SECOND, EventDateTime, B.XDateTime) AS 'Time'
            ,ROW_NUMBER() OVER (PARTITION BY B.XDateTime ORDER BY EventDateTime DESC) AS 'pc'
        
        FROM vw_A6Productivity AS "A"

        INNER JOIN
            (SELECT
                ID AS 'BID'
                ,EventDateTime AS 'XDateTime'
            
            FROM TABLE
            
            WHERE TYPE = 'X'
            
            GROUP BY 
                ID
                ,EventDateTime
            ) AS "B"

        ON A.ID= B.BID

        WHERE 
            EventDateTime < XDateTime -- Inner join filters for Nulls automatically
            AND STATUS = 'Y'
        
        ) AS "C"

    WHERE
        pc = 1
        
    ) AS "D"

WHERE dc = 1;