In bed at midnight! 1

[ChrisDodgson]

Hi,

I have a table which shows an 'In date & time' and an 'out date & time' for people in hospital. I have to calculate how many patients were occupying a bed at midnight per day, between any two dates that the user might choose...

For example:

patientid startdate enddate
1 01/04/2003 15:30 03/04/2003 10:00
2 15/04/2003 10:50 19/04/2003 08:00

For these patients, I would want to put in a start date of 01/04/2003 and an end date of 30/04/2003 and I would expect the output to be something like the following:

date patientid
01/04/2003 1
02/04/2003 1
15/04/2003 2
16/04/2003 2
17/04/2003 2
18/04/2003 2

The way I'm currently going about it is to have a lookup table of every date over a 10 year period (in a table called bd_date - values look as follows: 01/04/2003 00:00:00.) And my code reads something like as follows:

select bd_date.[date], patients.patient_id
from bd_date, patients
where bd_date between patients.startdate and patients.enddate

however it appears to include the enddate when I don't want it to...

Any help would be greatly appreciated!

Thanks,

Chris

 

[Jamfool]

select bd_date.[date], patients.patient_id
from bd_date, patients
where bd_date >= patients.startdate
and bd_date < patients.enddate

 

[JayKusch]

Using the BETWEEN clause causes the startdate and enddate to be INCLUSIVE in the range returned. You may want to use the >= and < conditionals like ...

select bd_date.[date], patients.patient_id
from bd_date, patients
where bd_date >= patients.startdate
and bd_date < patients.enddate



Thanks

J. Kusch

 

[Jamfool]

ummm i think iam very tired.

where patients.startdate >= bd_date
and patients.enddate < bd_date

 

[ChrisDodgson]

I've had a go with your codes, but what they don't actually do what I need them to...

If you apply your code to my example, using parameters of Startdate of 1st April 2003 and enddate of 30th April 2003 it counts patients 1 and 2 as being in bed at midnight on 3rd April and on the 19th respectively, whereas they were actually discharged earlier during the day.

I very much appreciate the help you're offering though.

Thanks,

Chris

 

[JamesLean]

Your expected results don't quite make sense to me. Midnight comes at the beginning of a day, not at the end. From the two sample patients you give, if you were counting the number of patients at midnight per day then I would expect the results to look like:

Date | Number of Patients
01/04/03 | 0
02/04/03 | 1
03/04/03 | 1
04/04/03 | 0
...
15/04/03 | 0
16/04/03 | 1
17/04/03 | 1
18/04/03 | 1
19/04/03 | 1
20/04/03 | 0
...

To get these results the query would look like this:

select d.date, count(distinct p.patientid)
from bd_date d left join patients p on d.date between p.startdate and p.enddate
where d.date between '20030401' and '20030430'
group by d.date
order by d.date

If you do want the results as per your original post (ie how many patients at the end of the day) then alter that query slightly:

select d.date, count(distinct p.patientid)
from bd_date d left join patients p on d.date + 1 between p.startdate and p.enddate
where d.date between '20030401' and '20030430'
group by d.date
order by d.date

--James

 

[ChrisDodgson]

James,

your answer gave me exactly what I needed... the logic of it was escaping me. A much appreciated star for you!

Thanks,

Chris