CODEWITHSUNDEEP
sqlsql-serversql-server-2005
si618
si618

Reputation: 16858

Create sequence partitioned by multiple columns and contiguous date

I'm trying to figure out how to create a sequence partitioned by multiple columns, where the sequence must reset once another (date based) column is not contiguous.

Problem: Hospital ADT (Admission/Discharge/Transfer) events occur at a particular point in time, but we want to turn these events into activities which have a duration (timespan), i.e. we have the start date, but don't have the end date, which is based on the next appropriate ADT event. We have done this in code, but also want to do it in SQL to improve performance. e.g. find patients who have spent more than 48 hours in ICU.

There are six different levels of site locations we want to record the duration of: facility, point of care, building, floor, room and bed.

Example:

Stream  Event  Started           Facility    PointOfCare  ...
1       1      2015-01-01 09:05  Hospital-A  ICU           
1       2      2015-01-02 13:10  Hospital-A  WARD-1
2       3      2015-02-10 12:00  Hospital-A  ICU           
2       4      2015-02-11 12:00  Hospital-A  ICU
2       5      2015-02-12 04:30  Hospital-A  WARD-2

So for each event we want to know how long they were in each particular site location. The end dates of the last activity in each stream are either null (still an inpatient) or the date patient was discharged.

Here's my current solution:

-- Create a sequence for each site location
INSERT INTO ADT_Activity_Sequence
SELECT 
  [Stream], 
  [Event],
  [Started],
  [Facility], 
  ROW_NUMBER() OVER (PARTITION BY [Stream], 
    ISNULL([Facility], [Event]) 
    ORDER BY [Started]) AS [FacilitySequence], 
  [PointOfCare], 
  ROW_NUMBER() OVER (PARTITION BY [Stream], 
    ISNULL([Facility], [Event]), 
    ISNULL([PointOfCare], [Event]) 
    ORDER BY [Started]) AS [PointOfCareSequence]
  -- and so on for all site locations
FROM ADT_Event
INNER JOIN ADT_Stream ON ADT_Event.Stream = Stream.Id

Example:

Stream  Event  Started           Facility    FacilitySequence  PointOfCare  PointOfCareSequence ...
1       1      2015-01-01 09:05  Hospital-A  1                 ICU          1 
1       2      2015-01-02 13:10  Hospital-A  2                 WARD-1       1
2       3      2015-02-10 12:00  Hospital-A  1                 ICU          1
2       4      2015-02-11 12:00  Hospital-A  2                 ICU          2
2       5      2015-02-12 04:30  Hospital-A  3                 WARD-2       1

Then create duration from the sequences:

INSERT INTO ADT_Activity_Duration
SELECT 
    [Stream], 
    [Event],
    [Started],
    [Facility], 
    [Sequence].[FacilitySequence],
    (
        -- Find most recent activity which is the first in current sequence
        SELECT TOP 1 [FacilitySequence].[Started] 
        FROM [ADT_Activity_Sequence] [FacilitySequence]
        WHERE [FacilitySequence].[Stream] = [Event].[Stream] AND [FacilitySequence].[FacilitySequence] = 1 AND [FacilitySequence].[Started] <= [Event].[Started]
        ORDER BY [FacilitySequence].[Started] DESC
    ) AS [FacilityStarted],
    (
        -- Find first activity in next sequence as this activities end date
        -- Last activity returns null, so activity uses stream end date if set
        ISNULL((                
            SELECT TOP 1 [FacilitySequence].[Started]
            FROM [ADT_Activity_Sequence] [FacilitySequence]
            WHERE [FacilitySequence].[Stream] = [Event].[Stream] AND [FacilitySequence].[FacilitySequence] = 1 AND [FacilitySequence].[Started] > [Event].[Started]
            ORDER BY [FacilitySequence].[Started]), [Stream].[Ended])
    ) AS [FacilityEnded],
    [PointOfCare], 
    [Sequence].[PointOfCareSequence],
    (
        SELECT TOP 1 [PointOfCareSequence].[Started] 
        FROM [ADT_Activity_Sequence] [PointOfCareSequence]
        WHERE [PointOfCareSequence].[Stream] = [Event].[Stream] AND [PointOfCareSequence].[PointOfCareSequence] = 1 AND [PointOfCareSequence].[Started] <= [Event].[Started]
        ORDER BY [PointOfCareSequence].[Started] DESC
    ) AS [PointOfCareStarted],
    (
        ISNULL((
            SELECT TOP 1 [PointOfCareSequence].[Started]
            FROM [ADT_Activity_Sequence] [PointOfCareSequence]
            WHERE [PointOfCareSequence].[Stream] = [Event].[Stream] AND [PointOfCareSequence].[PointOfCareSequence] = 1 AND [PointOfCareSequence].[Started] > [Event].[Started]
            ORDER BY [PointOfCareSequence].[Started]), [Stream].[Ended])
    ) AS [PointOfCareEnded]
    -- and so on for all site locations
FROM ADT_Event AS [Event]
INNER JOIN [ADT_Stream] AS [Stream] ON [Event].[Stream] = [Stream].[Id]
INNER JOIN [ADT_Activity_Sequence] [Sequence] ON [Event].[Id] = [Sequence].[Event]

Example:

Stream  Event  Started           Facility    FacilitySequence  FacilityStarted  FacilityEnded     PointOfCare  PointOfCareSequence  PointOfCareStarted  PointOfCareEnded  ...
1       1      2015-01-01 09:05  Hospital-A  1                 2015-01-01 09:05 2015-01-03 12:00  ICU          1                    2015-01-01 09:05    2015-01-02 13:10  
1       2      2015-01-02 13:10  Hospital-A  2                 2015-01-01 09:05 2015-01-03 12:00  WARD-1       1                    2015-01-02 13:10    2015-01-03 12:00
2       3      2015-02-10 12:00  Hospital-A  1                 2015-02-10 12:00 <NULL>            ICU          1                    2015-02-10 12:00    2015-02-12 04:30
2       4      2015-02-11 12:00  Hospital-A  2                 2015-02-10 12:00 <NULL>            ICU          2                    2015-02-10 12:00    2015-02-12 04:30
2       5      2015-02-12 04:30  Hospital-A  3                 2015-02-10 12:00 <NULL>            WARD-2       1                    2015-02-12 04:30    <NULL>

My problem is when the contiguous date sequence is broken, which happens when a patient is transferred from any site location, and then transferred back again, all within the same stream:

Stream  Event  Started           Facility    PointOfCare  ...
3       1      2015-03-01 09:05  Hospital-A  ICU           
3       2      2015-03-02 13:10  Hospital-A  WARD-1
3       3      2015-03-02 10:00  Hospital-A  ICU

Example:

Stream  Event  Started           Facility    FacilitySequence  PointOfCare  PointOfCareSequence ...
3       1      2015-03-01 09:05  Hospital-A  1                 ICU          1 
3       2      2015-03-02 13:10  Hospital-A  2                 WARD-1       1
3       3      2015-03-02 10:00  Hospital-A  3                 ICU          2

Note event #3 has a point of care sequence of 2, which is incorrect, it needs to be reset back to 1 due to event #2 being in a different location.

I've been going around in circles for a while now :) so any help appreciated, thanks!

Upvotes: 2

Views: 524

Answers (1)

ughai
ughai

Reputation: 9890

If I understand your question correctly you need continuous ROW_NUMBER(). you can use the difference of row numbers between stream ROW_NUMBER()and individual sequence to generate a group on which to order your row numbers for facility and point of care.

Since these are not directly grouped using Facility and PointofCare but based on their sequence of order, if a patient is switched back to the same facility or point of care again, the sequence is reset.

Use something like this. SQL Fiddle

;WITH CTE as 
(
    SELECT *,
    ROW_NUMBER() OVER(PARTITION BY Stream ORDER BY [Started]) as StreamSequence,
    ROW_NUMBER() OVER(PARTITION BY Stream ORDER BY [Started]) - ROW_NUMBER() OVER(PARTITION BY Facility ORDER BY [Started]) as FacilityGroup,
    ROW_NUMBER() OVER(PARTITION BY Stream ORDER BY [Started]) - ROW_NUMBER() OVER(PARTITION BY PointOfCare ORDER BY [Started]) as PointOfCareGroup
    FROM Stream
)
SELECT 
Stream, Event, Started, Facility, PointOfCare, StreamSequence,
ROW_NUMBER() OVER(PARTITION BY Stream,FacilityGroup ORDER BY [Started]) as FacilitySequence,
ROW_NUMBER() OVER(PARTITION BY Stream,PointOfCareGroup ORDER BY [Started]) as PointOfCareSequence
FROM CTE
ORDER BY Event;

You can generate your date range based on these sequence as required.

Output

| Stream | Event |                    Started |   Facility | PointOfCare | StreamSequence | FacilitySequence | PointOfCareSequence |
|--------|-------|----------------------------|------------|-------------|----------------|------------------|---------------------|
|      1 |     1 |  January, 01 2015 09:05:00 | Hospital-A |         ICU |              1 |                1 |                   1 |
|      1 |     2 |  January, 02 2015 13:10:00 | Hospital-A |      WARD-1 |              2 |                2 |                   1 |
|      2 |     3 | February, 10 2015 12:00:00 | Hospital-A |         ICU |              1 |                1 |                   1 |
|      2 |     4 | February, 11 2015 12:00:00 | Hospital-A |         ICU |              2 |                2 |                   2 |
|      2 |     5 | February, 12 2015 04:30:00 | Hospital-A |      WARD-2 |              3 |                3 |                   1 |
|      2 |     6 | February, 12 2015 05:30:00 | Hospital-A |         ICU |              4 |                4 |                   1 |

Upvotes: 5

Related Questions