March 25, 2015 at 2:33 pm
I have a rather tricky problem for you all to help me solve. I am on SQL Server 2012 and I have a table that has events that tell when a patient has moved from one status to another. The change in status that triggers an event record can be a change in their location, a change in their room or a change in their bed. Here is an example of the data:
Patient Location Room Bed InDateTime OutDateTime
123 TRIAGE T1 NA 1/1/1900 00:00 3/1/15 12:00
123 EMERGENCY E1 1 3/1/15 12:00 3/1/15 12:30
123 NEUROLOGY N1 1 3/1/15 12:30 3/1/15 13:30 <----
123 NEUROLOGY N1 2 3/1/15 13:30 3/1/15 14:00 <---90min
123 SURGERY S1 1 3/1/15 14:00 3/1/15 17:00
123 RECOVERY R1 1 3/1/15 17:00 3/1/15 18:00
123 NEUROLOGY N2 1 3/1/15 18:00 3/2/15 07:00 <---
123 NEUROLOGY N2 2 3/2/15 07:00 3/2/15 08:00 <--900min
DISCHARGED FROM NEUROLOGY 3/2/2015 AT 08:00 TO HOME
I am trying to track the patient's flow from location to location and the time spent in each location without regard to the room or bed. So I would have something like this:
123 EMERGENCY 30 minutes
123 NEUROLOGY 90 minutes (12:30 to 14:00)
123 SURGERY 180 minutes
123 RECOVERY 60 minutes
123 NEUROLOGY 900 minutes (3/1 at 18:00 to 3/2 at 8:00)
I have used LAG and been able to do a running total of the minutes:
LAG(DATEDIFF(MINUTE,IN_DTTM,OUT_DTTM)) OVER (PARTITION BY PATIENT ORDER BY IN_DTTM BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
But when I try to use this to sum consecutive minutes in the same department the system sorts all of the records by LOCATION and IN_DTTM and I get the sum of all minutes in NEUROLOGY (990 minutes) instead of two separate sums of 90 and 900. How do I tell it that the running total has to be within the same location and break at the next sequential location, even if I come back to the original location again afterwards?
I don't have the coding skill to use a cursor or any other procedural method, so is there a strictly T-SQL way to accomplish this, even if it involves subqueries or ctes?
Thanks in advance for you help.
March 25, 2015 at 4:07 pm
This is a gaps and islands problem. You can read about many solutions on the internet.
Here's an example with my preferred method.
CREATE TABLE PatientsLocations(
Patient int,
Location varchar(30),
Room CHAR(2),
Bed char(2),
InDateTime datetime,
OutDateTime datetime);
INSERT INTO PatientsLocations VALUES
(123, 'TRIAGE ','T1', 'NA', '1/1/1900 00:00', '3/1/15 12:00'),
(123, 'EMERGENCY ','E1', '1' , '3/1/2015 12:00', '3/1/15 12:30'),
(123, 'NEUROLOGY ','N1', '1' , '3/1/2015 12:30', '3/1/15 13:30'), ----
(123, 'NEUROLOGY ','N1', '2' , '3/1/2015 13:30', '3/1/15 14:00'),---90min
(123, 'SURGERY ','S1', '1' , '3/1/2015 14:00', '3/1/15 17:00'),
(123, 'RECOVERY ','R1', '1' , '3/1/2015 17:00', '3/1/15 18:00'),
(123, 'NEUROLOGY ','N2', '1' , '3/1/2015 18:00', '3/2/15 07:00'),---
(123, 'NEUROLOGY ','N2', '2' , '3/2/2015 07:00', '3/2/15 08:00');--900min
--DISCHARGED FROM NEUROLOGY 3/2/2015 AT 08:00 TO HOME
WITH ctePatientsLocations AS(
SELECT *,
DATEDIFF(MINUTE, NULLIF( InDateTime, '19000101'), NULLIF( OutDateTime, '19000101')) locminutes,
ROW_NUMBER() OVER (PARTITION BY Patient ORDER BY InDateTime)
- ROW_NUMBER() OVER (PARTITION BY Patient, Location ORDER BY InDateTime) Island
FROM PatientsLocations
)
SELECT Patient,
Location,
MIN(InDateTime) InDateTime,
MAX(OutDateTime) OutDateTime,
SUM(locminutes) Minutes
FROM ctePatientsLocations
GROUP BY Patient, Location, Island
ORDER BY Patient, InDateTime
DROP TABLE PatientsLocations
By the way, I've included DDL and sample data the way you should do it in future posts so we don't have to spend time preparing the scenario.
March 26, 2015 at 7:05 am
Luis, thank you for your response. I really appreciate the help. And thanks for the DDL. I apologize for not formatting things properly. I guess I didn't expect anyone to actually create the table and all... I will adhere to standards next time! 😉
March 26, 2015 at 9:57 am
There's no need to apologize as you're new here. But it's a good practice to do if you want to attract more answers faster. We use the tables and sample data to give tested solutions instead of a simple guess.
The next issue would be. Do you understand what you need to do? Do you have any questions?
March 26, 2015 at 10:21 am
Luis, I totally understood what to do and why from your excellent example. I had not remembered the whole "island" thing, frankly, but your response was incredibly instructive. I have created my stored procedure and I feel like a superhero to my users! 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply