Different Times for same date causing duplicates

Transact-SQL
1

Good Afternoon,
The intent of this report is to calculate the following:

  1. Average Length of stay (In hours) for all Observation patients.
  2. Median Length of stay (In hours) for all Observation patients.
  3. Percent of Observation patient whose stay were greater than 48 hours.

I've written the below report:
USE livedb

DROP TABLE #MaxAdm, #MaxObs

--ADMISSIONS
SELECT
Max(EventSeqID) AS MaxSeqAdm,
EventSeqID, VisitID, Code,
CONVERT(VARCHAR(10),(EffectiveDateTime),101) as Date,
CONVERT(VARCHAR(10),(EffectiveDateTime),108) as Time
INTO #MaxAdm
FROM AdmVisitEvents
WHERE EventDateTime BETWEEN '01/01/17 00:00:00' AND '01/31/17 23:59:59'
AND Code = 'ENADMOB'
GROUP BY EventSeqID, VisitID, Code, EffectiveDateTime

--DISCHARGES
SELECT Max(EventSeqID) AS MaxSeqAdm,
EventSeqID, VisitID, Code,
CONVERT(VARCHAR(10),(EffectiveDateTime),101) as Date,
CONVERT(VARCHAR(10),(EffectiveDateTime),108) as Time
INTO #MaxObs
FROM AdmVisitEvents
WHERE EventDateTime BETWEEN '01/01/17 00:00:00' AND '01/31/17 23:59:59'
AND Code = 'ENDISOB'
GROUP BY EventSeqID, VisitID, Code, EffectiveDateTime

--JOIN
SELECT *
FROM #MaxAdm A LEFT JOIN #MaxObs O ON A.VisitID = O.VisitID

This takes the most recent EventDateTime of the Admit, and Discharge, of observation patients, with the exception when the time is different for the same account. Basically someone edits the account to a slightly different time, and because I need the time for calculation mentioned above, I get duplicates, like below:

EventSeqID VisitID Code Date Time
10 AcctNo ENADMOB 1/3/2017 20:40:00
8 AcctNo ENADMOB 1/3/2017 20:48:00

Would someone explain, maybe even tweak my code, to accomplish this? At the end of the day I need the most recent activity for the Adm and Dis.

Thank you for your help,

Frank

2

Maybe this?:

SELECT 
    adm_dis.VisitID,
    MAX(adm_dis.MaxSeqAdm) AS EventSeqID_Admit,
    MAX(adm_dis.MaxSeqDis) AS EventSeqID_Discharge,
    CONVERT(varchar(10), MAX(CASE WHEN ave.Code = 'ENADMOB' THEN ave.EffectiveDateTime END), 101) AS Date_Admit,
    CONVERT(varchar(10), MAX(CASE WHEN ave.Code = 'ENADMOB' THEN ave.EffectiveDateTime END), 108) AS Time_Admit,
    CONVERT(varchar(10), MAX(CASE WHEN ave.Code = 'ENDISOB' THEN ave.EffectiveDateTime END), 101) AS Date_Discharge,
    CONVERT(varchar(10), MAX(CASE WHEN ave.Code = 'ENDISOB' THEN ave.EffectiveDateTime END), 108) AS Time_Discharge
FROM (
    SELECT	
    VisitID,
    Max(CASE WHEN Code = 'ENADMOB' THEN EventSeqID END) AS MaxSeqAdm,
    Max(CASE WHEN Code = 'ENDISOB' THEN EventSeqID END) AS MaxSeqDis
    FROM	AdmVisitEvents
    WHERE	EventDateTime >= '20170101' AND EventDateTime < '20170201'
    AND	Code IN ('ENADMOB', 'ENDISOB')
    GROUP BY VisitID
) AS adm_dis
INNER JOIN AdmVisitEvents ave ON ave.VisitID = adm_dis.VisitID and ave.EventSeqID IN (adm_dis.MaxSeqAdm, adm_MaxSeqDis)
GROUP BY adm_dis.VisitID
ORDER BY adm_dis.VisitID
1 Like
3

This appears to be working perfectly. I will review the logic so understand it going forward. Thank you very much for your help and quick response! Frank

4

You're welcome! Glad it helped :-).