Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

Calculate the interval of days between a discharge date and a subsequent admission date for the same Medical Record #

I have attached a list of admissions by sorted Medical Record and VisitId number (desc).  I am trying to calculate the interval of days between a patient's subsequent admission and their most recent discharge date.  If the patient was not discharged previously to the current admission, the interval is equal to zero.

Here is the code to generate the attached list:

select
 AD.UnitNumber as MedRec#, AD.AccountNumber, AD.VisitID,AD.AdmitDateTime, AD.DischargeDateTime, AD.PatientClass,RANK () over(partition by AD.UnitNumber order by AD.VisitID desc) as ARnk  
from
 livedb.dbo.AbstractData AD  
left
 outer join livedb.dbo.AbsDrgDiagnoses ADR  
on
 AD.VisitID=ADR.VisitID  
where
 AD.AdmitDateTime >='2015-12-01' and LEFT(AD.PatientClass,2)='IN' and ADR.DiagnosisSeqID=1  
order
 by AD.UnitNumber,AD.VisitID desc

What can I add to perform the caclualtion?

Thank you

Glen
Readmit_Sample.xlsx
0
GPSPOW
Asked:
GPSPOW
1 Solution
 
SharathData EngineerCommented:
check this
;with cte as (
select
 AD.UnitNumber as MedRec,
 AD.AccountNumber, 
 AD.VisitID,
 AD.AdmitDateTime, 
 AD.DischargeDateTime, 
 AD.PatientClass,
 RANK () over(partition by AD.UnitNumber order by AD.VisitID desc) as ARnk  
from
 livedb.dbo.AbstractData AD  
left
 outer join livedb.dbo.AbsDrgDiagnoses ADR  
on
 AD.VisitID=ADR.VisitID  
where
 AD.AdmitDateTime >='2015-12-01' and LEFT(AD.PatientClass,2)='IN' and ADR.DiagnosisSeqID=1)
select c1.*,coalesce(datediff(dd,c2.DischargeDateTime, c1.AdmitDateTime),0) Interval
  from cte c1
  left join cte c2 on c1.MedRec = c2.MedRec and c1.ARnk+1 = c2.ARnk 
order
 by c1.UnitNumber,c1.VisitID desc 

Open in new window

2
 
Chris LuttrellSenior Database ArchitectCommented:
If you are on SQL Server 2012 or later this will work, you can leave off the COALESCE if you are ok with NULL instead of 0:
SELECT
 AD.UnitNumber as MedRec#, AD.AccountNumber, AD.VisitID, AD.AdmitDateTime, AD.DischargeDateTime, AD.PatientClass,RANK () over(partition by AD.UnitNumber order by AD.VisitID desc) as ARnk,
 COALESCE(DATEDIFF(DAY, LEAD(AD.DischargeDateTime) OVER (PARTITION BY AD.UnitNumber ORDER BY AD.VisitID DESC), AD.AdmitDateTime),0) AS Interval 
from
 dbo.AbstractData AD  
left
 outer join dbo.AbsDrgDiagnoses ADR  
ON
 AD.VisitID=ADR.VisitID  
where
 AD.AdmitDateTime >='2015-12-01' and LEFT(AD.PatientClass,2)='IN' and ADR.DiagnosisSeqID=1  
order
 by AD.UnitNumber,AD.VisitID desc 

Open in new window

Results look like this
Query Results
0
 
GPSPOWAuthor Commented:
Thanks this worked.  I had to change C1.UnitNumber to C1.MedRec

Glen
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now