CODEWITHSUNDEEP
sqlsql-servert-sql
martin.softpro
martin.softpro

Reputation: 443

A query to show me the number of patients discharged and re-enter within 72 hours for the same cause

In SQL Server 2005 I have a table that stores incoming and outgoing patients in a hospital.

Table data stores are: patient's full name, date of admission, date of discharge, and the cause of the entrance to the hospital.

What I need is a query to show me the number of patients discharged and re-enter within 72 hours for the same cause.

the table structure is:

CREATE TABLE hospital(
     patient   varchar(50),    
     in        datetime, 
     out       datetime, 
     reason_id int
 );

This is the query:

SELECT DISTINCT H1.PATIENT 
FROM HOSPITAL H1 
   INNER JOIN (SELECT * FROM HOSPITAL) H2 ON DATEDIFF(HOUR,H1.IN,H2.OUT) < 72

Upvotes: 2

Views: 2968

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

If you actually want to look at the next admission within 72 hours, then the best bet is to use lag . . . assuming you have SQL Server 2012. Otherwise, this query does the same thing.

Well, I'll describe the answer. You can use a correlated subquery to get the next reason and next time. Then a where clause to meet the conditions:

where nextIn <= DATEADD(hh, 72, out) and
      nextReason = reason

[trouble uploading answer]

Upvotes: 0

meh
meh

Reputation: 129

Generally, you will select the record where they have been admitted for at least 72hr,right? so do this;

SELECT Patient FROM 
(
SELECT Patient, DATEDIFF ("HH", In,Out)HOURDIFF FROM hospital
)temp 
WHERE HOURDIFF > 72

You will need the reason_id field for when the patient is re-admitted and add that to Where clause that way you can filter further to get same admitted and re-admitted reason id.

Upvotes: 0

splattne
splattne

Reputation: 104040

select distinct h1.patient
from   Hospital h1 join Hospital h2 on h1.patient = h2.patient
where  h1.[in] < h2.[in]
and    h2.[in] < dateadd(hour, 72, h1.[out])
and    h1.reason_id = h2.reason_id

Upvotes: 4

Related Questions