SQL lite query - Merging time periods

user_11765770 0
Viewed 49

I am working with a SQLite RDB and have the following problem.

PID EID  EPISODETYPE    START_TIME          END_TIME
123 556 emergency_room  2020-03-29 15:09:00 2020-03-30 20:36:00 
123 558 ward            2020-04-30 20:35:00 2020-05-04 22:12:00 
123 660 ward            2020-05-04 22:12:00 2020-05-21 08:59:00 
123 661 icu             2020-05-21 09:00:00 2020-07-01 17:00:00

Basically, PID represents each patient unique identifier. They all have an episode identifier for all the different beds they occupy during a unique stay. What I wish to accomplish is to select all episodes from a single hospital stay and return it as the stay number.

I would want my query to result in this :

PID EID StayNumber
123 556 1
123 558 2
123 660 2    
123 661 2

1 st row is StayNumber as it's the first. As the 2nd, 3rd and 4th row are from the same hospital stay (we can tell by the overlapping OR relatively close start and end time period) they are all labeled StayNumber 2.

A hospital stay is defined as the period of time during which the patient never left the hospital.

I tried to write the query by starting off with a :

  • GROUP BY PID (to isolate the process for each individual patient)
  • Using datetime to compute a simple time difference rule but I have trouble writing down a query using the end time from a row and the start time from the next row.

Thank you in advance. I am a SQL learner

UPDATE ***

sqlite sql
1 Answers

Use window function LAG() to flag the groups for each hospital stay and window function SUM() to get the numbers:

SELECT PID, EID,
       SUM(flag) OVER (PARTITION BY PID ORDER BY START_TIME) StayNumber
FROM (
  SELECT *, 
    strftime('%s', START_TIME) - 
    strftime('%s', LAG(END_TIME, 1, datetime(START_TIME, '-1 hour')) OVER (PARTITION BY PID ORDER BY START_TIME)) > 60 flag
  FROM tablename
)

See the demo.
Results:

|PID | EID | StayNumber
|:-- | :-- | ---------:
|123 | 556 |          1
|123 | 558 |          2
|123 | 660 |          2
|123 | 661 |          2
user_104988280
Related