September 10, 2009 at 3:37 pm
I am trying to calculate the throughput time, I have patient arrive time in one temp table and exit time in other temp table. The patient can have more than one appointment during the day, and we need to match up the arrive time with the first exit time after the arrive time.
Possible cases:
Arrive, --
Arrive, Exit
Arrive, Arrive, Exit
Arrive, Exit, Arrive, Exit ???????
Arrive, Exit, Arrive, --
The problem is the event we using for exit time can occur more than one times.
I can not figure out how to do the case like Arrive, Exit, ARRIVE, EXIT for 2nd appointment.
I have 2 temp tables that have the following values.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB.dbo.#Arrive','U') IS NOT NULL
DROP TABLE #Arrive
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB.dbo.#Exit','U') IS NOT NULL
DROP TABLE #Exit
--===== Create the test table with
CREATE TABLE #Arrive
(
ArriveID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
PatientID INT,
ArriveTime DATETIME
)
--===== Create the test table with
CREATE TABLE #Exit
(
ExitID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
PatientID INT,
ExitTime DATETIME
)
----===== All Inserts into the IDENTITY column
--SET IDENTITY_INSERT #Arrive ON
--===== Insert the test data into the test table
INSERT INTO #Arrive
(PatientID, ArriveTime)
SELECT 100,'Oct 17 2007 08:00AM' UNION ALL
SELECT 200,'Oct 17 2007 08:15AM' UNION ALL
SELECT 200,'Oct 17 2007 10:30AM' UNION ALL
SELECT 300,'Oct 17 2007 08:15AM' UNION ALL
SELECT 300,'Oct 17 2007 11:30AM' UNION ALL
SELECT 400,'Oct 17 2007 08:10AM' UNION ALL
SELECT 500,'Oct 17 2007 08:00AM' UNION ALL
SELECT 700,'Oct 17 2007 09:00AM' UNION ALL
SELECT 700,'Oct 17 2007 11:00AM' UNION ALL
SELECT 800,'Oct 17 2007 09:15AM'
----===== All Inserts into the IDENTITY column
--SET IDENTITY_INSERT #Exit ON
--===== Insert the test data into the test table
INSERT INTO #Exit
(PatientID, ExitTime)
SELECT 100,'Oct 17 2007 09:00AM' UNION ALL
SELECT 200,'Oct 17 2007 09:45AM' UNION ALL
SELECT 200,'Oct 17 2007 09:50AM' UNION ALL
SELECT 200,'Oct 17 2007 11:15AM' UNION ALL
SELECT 200,'Oct 17 2007 11:30AM' UNION ALL
SELECT 300,'Oct 17 2007 01:15PM' UNION ALL
SELECT 300,'Oct 17 2007 01:20PM' UNION ALL
SELECT 400,'Oct 17 2007 11:20AM' UNION ALL
SELECT 400,'Oct 17 2007 11:30AM' UNION ALL
SELECT 600,'Oct 17 2007 08:15AM' UNION ALL
SELECT 700,'Oct 17 2007 10:05AM' UNION ALL
SELECT 700,'Oct 17 2007 10:10AM' UNION ALL
SELECT 800,'Oct 17 2007 09:09AM'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Arrive OFF
SET IDENTITY_INSERT #Exit OFF
Could you please tell me how to do this?
This is my expect results:
PatientID ArriveTime ExitTime ThroughputTime (HH:mm)
----------- ----------------------- ----------------------- -----------------------
100 2007-10-17 08:00:00.000 2007-10-17 09:00:00.00001:00
200 2007-10-17 08:15:00.000 2007-10-17 09:45:00.00001:30
200 2007-10-17 10:30:00.000 2007-10-17 11:15:00.00000:45
300 2007-10-17 08:15:00.000 2007-10-17 13:15:00.00005:00
300 2007-10-17 11:30:00.000 2007-10-17 13:15:00.00001:45
400 2007-10-17 08:10:00.000 2007-10-17 11:20:00.00003:10
500 2007-10-17 08:00:00.000 ---------- ---
700 2007-10-17 09:00:00.000 2007-10-17 10:05:00.00001:05
700 2007-10-17 11:00:00.000 ---------- ---
800 2007-10-17 09:15:00.000 ---------- ---
Thanks for your help.
September 10, 2009 at 4:57 pm
First of all, thank you for providing DDL together with obviously carefully designed sample data! Good job!
Following please find a proposal that will give you -almost- the expected result (Throughput time for first visit of patient 200 differs, but it looks like a typo on your side...).
;WITH
cte AS (
SELECT
a.patientid AS patientid,
a.arrivetime,
e.exittime,
CONVERT(CHAR(23),exittime,121) AS d,
ROW_NUMBER () OVER(PARTITION BY a.patientid,arrivetime ORDER BY exittime) AS row
FROM #Arrive a
INNER JOIN #Exit e ON a.patientid = e.patientid
WHERE arrivetime= e.exittime
)
AND cte.arrivetime IS NULL
)
SELECT
patientid,
arrivetime,
d exittime,
CONVERT(CHAR(5),dateadd(mi,datediff(mi,arrivetime,exittime),0),108) AS [ThroughputTime (HH:mm)]
FROM cte
WHERE row=1
UNION ALL
SELECT
patientid,
arrivetime,
d exittime,
'---'
FROM cte2
WHERE row=1
ORDER BY patientid, arrivetime
Edit: I hope you're on SS2K5, since the Row_Number function won't work.... (missed the forum you posted in...)
September 11, 2009 at 8:33 am
Unfortuneatly, we need a solution for SLQ 2000.
Our DBA is working on getting 2005 on our test server so we can test the existing app, written for 2000, but is still in the works. Too many projects, not enough time or hardware. Until that happens, no ROWNUMBER for us.
Cathy Greensfelder (co-worker of Dung Ly)
September 11, 2009 at 12:47 pm
This SQL reproduces your expected results on a SQL 2000 server.
SELECT
A.PatientId AS PatientID,
A.ArriveTime AS ArriveTime,
MIN(E.ExitTime) AS ExitTime,
CONVERT(varchar(5), MIN(E.ExitTime) - A.ArriveTime, 108) AS ThroughputTime
FROM #Arrive A
LEFT OUTER JOIN #Exit E ON (
A.PatientId = E.PatientId
AND A.ArriveTime < E.ExitTime
AND DATEDIFF(day, A.ArriveTime, E.ExitTime) = 0
)
GROUP BY A.PatientId, A.ArriveTime
ORDER BY A.PatientId, A.ArriveTime
I'm assuming here that the ArriveTime will always be earlier than the ExitTime for a given patient appointment (i.e. no zero-duration appointments), and that a patient appointment always takes place within a single day and doesn't continue into the following day.
The final part of the JOIN predicate: DATEDIFF(day, A.ArriveTime, E.ExitTime) = 0 can be removed and the SQL query will still produce your expected results with the supplied test data, but I added it to avoid possible spurious results when the ArriveTime and minimum ExitTime greater that this ArriveTime are on different days. This could happen if an #Exit table record of a particular patient appointment is missing, but an #Exit table record of a later appointment for the same patient exists.
If the #Arrive and #Exit tables are large, you would get a performance improvement, by changing the primary keys to nonclustered indexes and creating clustered indexes on the PatientId and ArriveTime columns in the case of the #Arrive table, and the PatientId and ExitTime columns in the case of the #Exit table.
September 11, 2009 at 12:54 pm
Hi,
Edit: Please disregard. Andrews solution (see previous post) is much easier and performs a lot better.
it's a little more complicated in SS2K (at least in terms of readability), but not impossible...
I hope it'll run on S2K since I don't have any system to test against.
An index on PatientID and ArriveTime on table #Arrive and on PatientID and ExitTime on table #Exit should help performance.
Another option would be to insert the two subselects into a temp table to avoid the UNION operation.
If this query is performance critical you should also think about a different option to display nonexisting ExitTimes and Throughput since both require a reformatting.
-- select all visits with valid exit time
SELECT
a.patientid AS patientid,
a.arrivetime,
MIN(CONVERT(CHAR(23),exittime,121)) AS ExitTime,
MIN(CONVERT(CHAR(5),dateadd(mi,datediff(mi,arrivetime,exittime),0),108)) AS [ThroughputTime (HH:mm)]
FROM #Arrive a
INNER JOIN #Exit e ON a.patientid = e.patientid
WHERE arrivetime<EXITTIME
GROUP BY a.patientid ,a.arrivetime
UNION ALL
-- select all visits with invalid or no exit time and no arrival time in previous select
SELECT
a.patientid AS patientid,
a.arrivetime,
'---' AS ExitTime,
'---'
FROM #Arrive a
LEFT OUTER JOIN #Exit e ON a.patientid = e.patientid
LEFT OUTER JOIN
(SELECT
a.patientid AS patientid,
a.arrivetime
FROM #Arrive a
INNER JOIN #Exit e ON a.patientid = e.patientid
WHERE arrivetime= e.exittime
)
AND cte.arrivetime IS NULL
GROUP BY a.patientid, a.arrivetime
ORDER BY patientid, arrivetime
-- result set as requested
September 14, 2009 at 7:40 am
Actually, we CAN have no exit time for the patient and we CAN have the exit time before the arrive time.
The arrive time is the time the patient checks in with the station desk clerk and the clerk marks him/her as "SHOW" for the appointment. The exit time is when the patient comes back and books his/her follow up appointment. Since not every patient books a follow up appointment, we have a fair number of appointments every day with no exit time. We will just report them as unknown. And it is possible (but not likely) to generate an exit time before the arrive time if the patient decides to book an appointment with a clinic earlier in the day, BEFORE checking in for today's appointment. Unorthodox, but possible.
We know it's inaccurate but it's what we've got. All the clinics would have to change their process and add a step to get good exit times and it would increase work load for someone. Someone higher up will have to decides it's worthwhile first. So we need a report! 🙂
September 14, 2009 at 8:01 am
Andrew -
I knew there was a simple solution but neither one of us could quite see it.
When Ly gets in and gives it a try with the full data, he'll let you know how it worked out. (I get in a couple hours before he does.)
Thanks, everyone!
September 14, 2009 at 9:20 am
It works great. Again thank you for your helps.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply