When I use this code it will return all instances of the Case ID when value has either been First Contact Resolution or Resolved at some point in it's time stamp history. I need to isolate the most recent timestamp of either of the two for a given caseid.
SELECT
MAX(DATEADD(hh,-30,timestamp)) [Time Stamp],
caseid [Case ID],
value [Value]
FROM
svb_caseupdate
WHERE
DATEADD(hh,-7,timestamp) >= DATEADD(day,-30,GETDATE())
AND value IN ('First Contact Resolution','Resolved')
GROUP BY caseid, value
For example, if both Resolved and First Contact Resolution exist in the time stamp history it returns both values, however I need it to return the most recent of either of the two.
| Case ID | Time Stamp | Value |
|---|---|---|
| E575E0B7-C036-EE11-BDF4-6045BD006016 | 2023-08-09 09:56:02.000 | First Contact Resolution |
| E575E0B7-C036-EE11-BDF4-6045BD006016 | 2023-07-19 11:09:23.000 | Resolved |
The result should be:
| Case ID | Time Stamp | Value |
|---|---|---|
| E575E0B7-C036-EE11-BDF4-6045BD006016 | 2023-08-09 09:56:02.000 | First Contact Resolution |
