SQL Server episode identification

Question

I am working with a blood pressure database in SQL Server which contains patient_id, timestamp (per minute) and systolicBloodPressure.

My goals are to find:

1. the number of episodes in which a patient is under a certain blood pressure threshold

   An episode consists of the timestmap where the patient drops below a certain threshold until the timestamp where the patient comes above the threshold.

2. the mean blood pressure per episode per patient

3. the duration of the episode per episode per patient

What I have tried so far:

I am able to identify episodes by just making a new column which sets to 1 if threshold is reached.

    select *
    , CASE
    when sys < threshold THEN '1'
    from BPDATA

However , I am not able to 'identify' different episodes within the patient; episode1 episode 2 with their relative timestamps.

Could someone help me with this? Or is there someone with a better different solution?

EDIT: Sample data with example threshold 100

    ID          Timestamp      SysBP      below Threshold
    ----------------------------------------------------
    1             9:38          110       Null
    1             9:39          105       Null
    1             9:40          96        1
    1             9:41          92        1 
    1             9:42          102       Null
    2             12:23         95        1
    2             12:24         98        1
    2             12:25         102       Null
    2             12:26         104       Null
    2             12:27         94        1
    2             12:28         88        1  
    2             12:29         104       Null

Answer 1

Thanks for the sample data.

This should work:

declare @t table (ID int, Timestamp time, SysBP int, belowThreshold bit)
insert @t 
values
(1,              '9:38',          110, null),
(1,              '9:39',          105, null),
(1,              '9:40',           96, 1),
(1,              '9:41',           92, 1),
(1,              '9:42',          102, null),
(2,             '12:23',           95, 1),
(2,             '12:24',           98, 1),
(2,             '12:25',          102, null),
(2,             '12:26',          104, null),
(2,             '12:27',           94, 1),
(2,             '12:28',           88, 1),
(2,             '12:29',          104, null)

declare @treshold int = 100

;with y as (
    select *, case when lag(belowThreshold, 1, 0) over(partition by id order by timestamp) = belowThreshold then 0 else 1 end epg
    from @t
),
z as (
    select *, sum(epg) over(partition by id order by timestamp) episode
    from y
    where sysbp < @treshold
)
select id, episode, count(episode) over(partition by id) number_of_episodes_per_id, avg(sysbp) avg_sysbp, datediff(minute, min(timestamp), max(timestamp))+1 episode_duration
from z
group by id, episode

Answer 2

This answer relies on LEAD() and LAG() functions so only works on 2012 or later:

Setup:

CREATE TABLE #bloodpressure 
(
    Patient_id int,
    [TimeStamp] SmallDateTime,
    SystolicBloodPressure INT
)

INSERT INTO #bloodpressure
VALUES
(1, '2017-01-01 09:01', 60),
(1, '2017-01-01 09:02', 55),
(1, '2017-01-01 09:03', 60),
(1, '2017-01-01 09:04', 70),
(1, '2017-01-01 09:05', 72),
(1, '2017-01-01 09:06', 75),
(1, '2017-01-01 09:07', 60),
(1, '2017-01-01 09:08', 50),
(1, '2017-01-01 09:09', 52),
(1, '2017-01-01 09:10', 53),
(1, '2017-01-01 09:11', 65),
(1, '2017-01-01 09:12', 71),
(1, '2017-01-01 09:13', 73),
(1, '2017-01-01 09:14', 74),
(2, '2017-01-01 09:01', 70),
(2, '2017-01-01 09:02', 75),
(2, '2017-01-01 09:03', 80),
(2, '2017-01-01 09:04', 70),
(2, '2017-01-01 09:05', 72),
(2, '2017-01-01 09:06', 75),
(2, '2017-01-01 09:07', 60),
(2, '2017-01-01 09:08', 50),
(2, '2017-01-01 09:09', 52),
(2, '2017-01-01 09:10', 53),
(2, '2017-01-01 09:11', 65),
(2, '2017-01-01 09:12', 71),
(2, '2017-01-01 09:13', 73),
(2, '2017-01-01 09:14', 74),
(3, '2017-01-01 09:12', 71),
(3, '2017-01-01 09:13', 60),
(3, '2017-01-01 09:14', 74)

Now using Lead And Lag to find the previous rows values, to find whether this is the beginning or end of a sequence of low blood pressures, in combination with a common table expression. Using a UNION of start and end events ensures that an event which covers just one minute is recorded as both a start and an end event.

;WITH CTE
AS
(
    SELECT *, 
        LAG(SystolicBloodPressure,1) 
            OVER (PaRTITION BY Patient_Id ORDER BY TimeStamp) As PrevValue, 
        Lead(SystolicBloodPressure,1) 
            OVER (PaRTITION BY Patient_Id ORDER BY TimeStamp) As NextValue  
    FROM #bloodpressure
),
CTE2
AS
(
    -- Get Start Events (EventType 1)
    SELECT 1 As [EventType],  Patient_id, TimeStamp,
          ROW_NUMBER() OVER (ORDER BY Patient_id, TimeStamp) AS RN
    FROM CTE
    WHERE (PrevValue IS NULL AND SystolicBloodPressure < 70) OR 
          (PrevValue >= 70 AND SystolicBloodPressure < 70)
    UNION
    -- Get End Events (EventType 2)
    SELECT 2 As [EventType],  Patient_id, TimeStamp, 
           ROW_NUMBER() OVER (ORDER BY Patient_id, TimeStamp) AS RN
    FROM CTE
    WHERE (NextValue IS NULL AND SystolicBloodPressure < 70 ) OR 
          (NextValue >= 70 AND SystolicBloodPressure < 70)
)
SELECT C1.Patient_id, C1.TimeStamp As EventStart, C2.TimeStamp As EventEnd 
FROM CTE2 C1
INNER JOIN CTE2 C2
    ON C1.Patient_id = C2.Patient_id AND C1.RN = C2.RN
WHERE C1.EventType = 1 AND C2.EventType = 2
ORDER BY C1.Patient_id, C1.TimeStamp