Determining most recent time stamp between two values with the same ID

Question

I have the following code that queries the below data table that lists a couple Case IDs and each has a number of time stamps associated with status changes. I need to pull First Contact Resolution and Resolved and then select the one with the most recent time stamp of the two. I attempted this with a CTE and using MAX() but it's still providing CASE IDs with both First Contact Resolution and Resolved statuses. Where am I going wrong?

WITH most_recent_status (Status, CaseId, TimeStamp) AS (
    SELECT
        svb_newvalue,
        svb_caseid,
        svb_timestamp
    FROM Table1
    WHERE svb_newvalue IN ('First Contact Resolution','Resolved')
)

SELECT
    CaseId [Case ID],
    MAX(TimeStamp) [Time Stamp],
    Status
    FROM
        most_recent_status
GROUP BY CaseId, TimeStamp, Status

Data table:

Case ID	Time Stamp	Status
6045BD006793	7/27/23 15:19	Resolved
6045BD006793	7/26/23 17:32	First Contact Resolution
6045BD006793	7/27/23 15:17	Active
6045BD006239	7/18/23 17:46	Pending Internal
6045BD006239	7/18/23 17:57	Routed
6045BD006239	7/18/23 18:03	First Contact Resolution
6045BD006239	7/18/23 19:19	Routed
6045BD006239	7/18/23 16:36	Resolved
6045BD006239	7/18/23 16:05	Routed

The Resulting table:

Case ID	Time Stamp	Status
6045BD006793	7/27/23 15:19	Resolved
6045BD006793	7/26/23 17:32	First Contact Resolution
6045BD006239	7/18/23 18:03	First Contact Resolution
6045BD006239	7/18/23 16:36	Resolved

What should happen:

Case ID	Time Stamp	Status
6045BD006793	7/27/23 15:19	Resolved
6045BD006239	7/18/23 18:03	First Contact Resolution

Answer 1

You can use this script. Keep in mind that Craig has a good point and you might want to add that to the query.

WITH cte
AS (SELECT
             t.svb_caseid,
             MAX(t.svb_timestamp) AS LastTimeStamp
    FROM     @Table1 AS t
    WHERE    t.svb_newvalue IN ( 'First Contact Resolution', 'Resolved' )
    GROUP BY t.svb_caseid)
SELECT
     a.svb_caseid AS [Case ID],
     a.svb_timestamp AS [Time Stamp],
     a.svb_newvalue AS [Status]
FROM cte AS c
     CROSS APPLY (
                    SELECT   TOP (1)
                             t.svb_caseid,
                             t.svb_timestamp,
                             t.svb_newvalue
                    FROM     @Table1 AS t
                    WHERE    t.svb_caseid = c.svb_caseid
                             AND t.svb_timestamp = c.LastTimeStamp
                             AND t.svb_newvalue IN ( 'First Contact Resolution', 'Resolved' )
                    ORDER BY t.svb_newvalue ASC
                 /*Add logic to see which is more important*/
                 ) a;