CODEWITHSUNDEEP
sqlsql-serverdatetimemin
AndrewMo
AndrewMo

Reputation: 149

Finding MIN nvarchar related to datetime

I have a table that contains a list of patient services and when they arrived to that service. I want to be able to pull just the first service after the emergency department. Here is the table:

    Service                  DateTimeIn
    H EMERGENCY MEDICINE    2013-01-01 10:43:00.000
    H MEDICINE E            2013-01-06 23:43:00.000
    H MEDICINE E            2013-01-07 17:18:00.000
    H MEDICINE ICU          2013-01-01 15:38:00.000
    H MEDICINE ICU          2013-01-07 00:49:00.000
    H MEDICINE ICU          2013-01-08 04:36:00.000

When I use MIN(service) where service <>'H EMERGENCY MEDICINE' it pulls the service alphabetically, which isn't what I need. I need the first service associated with the min time after the EMERGENCY MEDICINE service. In this case it would be H MEDICINE ICU. How should I arrange my query logic so that it pulls the min service based on the min datetimein?

Thank you

Upvotes: 1

Views: 245

Answers (2)

Jason W
Jason W

Reputation: 13179

I know this is old, but it bugged me that the other answer doesn't return correct answer if 2 emergency records occur next to each other. The 1st record would incorrectly show the 2nd record instead of null since in that case there would not be a non-emergency record between the 2 emergencies. I stumbled on this looking up another query, so I wanted to offer an update in case others stumbled on this.

The query below uses an OUTER APPLY instead of a CROSS APPLY and ROW_NUMBER to get the right next service.

DECLARE @S TABLE (
    [Service] NVARCHAR(40),
    [DateTimeIn] DATETIME,
    [PatientId] INT
)

INSERT @S
    SELECT 'H EMERGENCY MEDICINE', '2013-01-01 10:43:00.000', 1
    UNION ALL SELECT 'H MEDICINE E', '2013-01-06 23:43:00.000', 1
    UNION ALL SELECT 'H MEDICINE E', '2013-01-07 17:18:00.000', 1
    UNION ALL SELECT 'H MEDICINE ICU', '2013-01-01 15:38:00.000', 1
    UNION ALL SELECT 'H EMERGENCY MEDICINE', '2013-01-07 1:41:00.000', 1
    UNION ALL SELECT 'H EMERGENCY MEDICINE', '2013-01-07 1:43:00.000', 1
    UNION ALL SELECT 'H MEDICINE ICU', '2013-01-07 00:49:00.000', 1
    UNION ALL SELECT 'H MEDICINE ICU', '2013-01-08 04:36:00.000', 1

DECLARE @PatientId INT = 1
;WITH CTE AS (
    SELECT *,
        CASE WHEN S.Service = 'H EMERGENCY MEDICINE' THEN 1 ELSE 0 END AS IsEmergency,
        ROW_NUMBER() OVER ( ORDER BY DateTimeIn ) AS RowNum
    FROM @S S
    WHERE S.PatientId = @PatientId
)
    SELECT
        E.Service,
        E.DateTimeIn,
        N.NextService,
        N.NextDateTimeIn
    FROM CTE E
        OUTER APPLY (
            SELECT TOP 1
                Service AS NextService,
                DateTimeIn AS NextDateTimeIn
            FROM CTE
            WHERE IsEmergency = 0
                AND RowNum = E.RowNum + 1
        ) N
    WHERE IsEmergency = 1

This will then output:

Service                   DateTimeIn              NextService               NextDateTimeIn
------------------------- ----------------------- ------------------------- -----------------------
H EMERGENCY MEDICINE      2013-01-01 10:43:00.000 H MEDICINE ICU            2013-01-01 15:38:00.000
H EMERGENCY MEDICINE      2013-01-07 01:41:00.000 NULL                      NULL
H EMERGENCY MEDICINE      2013-01-07 01:43:00.000 H MEDICINE E              2013-01-07 17:18:00.000

Upvotes: 0

Jon Senchyna
Jon Senchyna

Reputation: 8047

You need to filter your query down to only the rows that occurred after the 'H EMERGENCY MEDICINE' entry, sort by DateTimeIn, and then grab the first one (TOP 1). A pseudo-code solution would look similar to this:

-- Get all Emergency visits for a given patient
WITH CTE_EmergencyVisits AS (
SELECT DateTimeIn
    FROM   [ServicesTable]
    WHERE  Services = 'H EMERGENCY MEDICINE'
    AND    [PatientFilter]
)
SELECT SVC.Service,
       SVC.DateTimeIn
-- For each emergency visit...
FROM CTE_EmergencyVisits E
-- ... get the first service that occurred afterward
CROSS APPLY (
    SELECT TOP 1
           Service,
           DateTimeIn
    FROM [ServicesTable] S
    WHERE S.[PatientIdentifier] = E.[PatientIdentifier]
    AND   S.DateTimeIn > E.DateTimeIn
    ORDER BY
          DateTimeIn ASC
) SVC

Upvotes: 1

Related Questions