sql statement cant be done

[bookouri]

I need help with an SQL problem. Ive been working on this one for a long time and can not come up with a solution.

Lets say we have a hospital room tracking application. We have three tables. Table 1 is the patient table, (patient_name, patient_id) Table 2 is a room table (room_bed_Description, bed_id) Table 3 is the bed_history table (bed_id, date_assigned, Time_assigned, patient_id).

I need a SQL script to select a specific date in the past and list the patient names and numbers of any patient that was assigned to that bed/room at any time during that date.

target date: 01/01/2001

room1 John Doe 12/31/2000 0900
Jim Doe 01/01/2001 1000
room2 Sue Smith 11/01/2000 2300
room3 Jim Smith 01/01/2001 0800
room4 Bob Smith 11/01/2000 2330
Bill Smith01/01/2001 0800
Sue Doe 01/01/2001 2300
Room5 -------- ------
Room6 -------- ------
Etc….


It’s easy enough to come up with any patient assigned to the room on the target date, but the problem I can’t overcome is how to select a patient who was assigned to the room before the target date. If I select for date_assigned = target_date and one patient was in the room for the last week he will not show up on the target date. If I select for date_assigned <= target_date I will have a list of everyone who was EVER assigned to the room before or on that date. They may have been in there a year previously and they will still be listed. There is no indicator in the bed_history table to determine when someone left. I know when they were assigned but not when they left. I know when the next patient was assigned that bed.

I can not modify the underlying database or the data collected. I have to make this work with the existing data structure. Any suggestions would be appreciated, or if the task is impossible I’d like to know.

 

[RafaelCintron]

First construct the following view:

create or replace view bed_occupation as

select c1.bed_id bed,
        max(c1.date_assigned) from,
        min(c2.date_assigned) to,
        c1.patient_id patient 
 from bed_history c1, 
      bed_history c2 
 where c1.bed_id = c2.bed_id 
 and   c1.date_assigned < c2.date_assigned
 group by c1.bed_id, c1.patient_id
 UNION ALL
 select bed_id,
        date_assigned,
        sysdate,
       id_paciente 
 from bed_history z
 where date_assigned = 
 (select max(date_assigned)
  from bed_history j
  where j.bed_id = z.bed_id)

and then you just issue a query like this

select * 
from bed_occupation
where '03/02/2001' between to_char(from,'dd/mm/yyyy') and to_char(to,'dd/mm/yyyy')

This method assumes that a bed gets occupied as soon as the last patient leaves the hospital. There's not much that can be done about this, since you don't register the date a
patient leaves the room.

I hope this solves your problem.
Good luck!