Many to many join based on datetimes
I am working on a report in a hospital inpatient scenario. I have two tables.
1) Wards: shows the time on a ward
2) Consultant (care): shows the period where a consultant was in care of a patient.
Say a patient is admitted onto ward A at 9am on July 1st. This patient is under the care of consultant 1.
The patient is then transferred to the care of consultant 2 at 14:00 on 2nd July. The patient is still on ward A.
The patient is then transferred to Ward B still under the care of consultant 2 at 10 am on 3rd July.
Let us say the patient is then discharged at 4pm on 3rd July.
The wards table has two entries. One for ward A and another for ward b.
The consultant care table has two entries. One for consultant 1 and another for consultant 2.
I need to get the accurate consultant ward combination i.e. I need 3 rows of data showing the consultant ward combination.
Ward A : Consultant 1
Ward A : Consultant 2
Ward B : Consultant 2
Is this possible?
If it helps, each table has a unique patient identifier. The ward table contains the start and end datetimes of each ward stay. The consultants table contains the start and end times of each period of consultant care.
As you can imagine, one ward stay can encompass many periods of care under different consultants. Also the period of care by a consultant can encompass stays on many different wards.
Any ideas on how to solve this problem?