# Many to many join based on datetimes

• Jul 22, 2013, 05:07
EssexSam
Many to many join based on datetimes
Hi

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.

I.e.
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?

Many thanks

EssexSam
• Jul 22, 2013, 09:34
r937
my suggestion is for you to throw up some representative and realistic sample data in each table (obviously, obfuscating the identifying values) and indicate which rows should be joined

it's not apparent from your narrative how the tables should be joined and on which columns
• Jul 23, 2013, 03:41
EssexSam
Thanks for the suggestion r937.

Here is some terminology. An episode is the period of care by a consultant. Each episode within a hospital attendance is numbered sequentially.

Here is the ward stay data.

 PatientID WardCode Ward Start Date Time Ward End Date Time Patient1 AM 2013-02-15 07:00:00 2013-02-17 07:00:00 Patient1 SR 2013-02-17 07:00:00 2013-02-21 07:00:00 Patient1 KM 2013-02-21 07:00:00 2013-04-21 07:00:00 Patient1 BM 2013-04-21 08:00:00 2013-05-13 07:00:00 Patient1 AE 2013-05-13 08:00:00 2013-05-14 07:00:00 Patient1 LC 2013-05-14 07:00:00 2013-05-23 13:43:00

Here is the episodes data:

 PatientID Episode Number Episode Start Date Time Episode End Date Time Consultant Code Patient1 1 2013-02-15 07:00:00 2013-02-17 08:00:00 C614 Patient1 2 2013-02-17 08:00:00 2013-02-21 08:00:00 C519 Patient1 3 2013-02-21 08:00:00 2013-04-21 09:00:00 C339 Patient1 4 2013-04-21 09:00:00 2013-05-14 07:00:00 C448 Patient1 5 2013-05-14 07:00:00 2013-05-23 13:43:00 C436

Here is the data I want to replicate. This data only has dates and not times, but has the correct ward consultant combinations.

 PatientID Ward Code Episode Number Ward Consultant Start Date Ward Consultant End Date Consultant Code Patient1 AM 1 2013-02-15 2013-02-17 C614 Patient1 SR 1 2013-02-17 2013-02-17 C614 Patient1 SR 2 2013-02-17 2013-02-21 C519 Patient1 KM 2 2013-02-21 2013-02-21 C519 Patient1 KM 3 2013-02-21 2013-04-21 C339 Patient1 BM 3 2013-04-21 2013-04-21 C339 Patient1 BM 4 2013-04-21 2013-05-13 C448 Patient1 AE 4 2013-05-13 2013-05-14 C448 Patient1 LC 5 2013-05-14 2013-05-14 C436

r937: I hope you don't mind me saying as I think you are the author of Simply SQL, that you suggested a great way of not using 'old-style' joins in a correlated sub-query. I had been using the old-style joins till I came across your suggested solution. On the basis of your suggestion, I bought Simply SQL and am working my way through it.
• Jul 23, 2013, 03:43
EssexSam
I should have said, at the moment, I am joining on patientID and the start and end date times of each table, but I am not quite there is getting the data I want.
There are no other useful fields that can be used to join the tables that will get me the data I want.
• Jul 23, 2013, 15:41
r937
okay, i used your test data and got pretty much exactly what you wanted
Code:

```SELECT wardstays.patientid     , wardstays.wardcode     , episodes.episodenumber     , DATE(GREATEST(wardstays.startdatetime                     ,episodes.startdatetime)) AS wardconsultant_startdate     , DATE(LEAST(wardstays.enddatetime                 ,episodes.enddatetime)) AS wardconsultant_enddate     , episodes.consultantcode   FROM wardstays INNER   JOIN episodes     ON episodes.patientid = wardstays.patientid   AND episodes.enddatetime  >= wardstays.startdatetime   AND episodes.startdatetime <= wardstays.enddatetime  ORDER     BY wardstays.patientid     , episodes.episodenumber     , wardconsultant_startdate     , wardconsultant_enddate Patient1        AM        1        2013-02-15        2013-02-17        C614 Patient1        SR        1        2013-02-17        2013-02-17        C614 Patient1        SR        2        2013-02-17        2013-02-21        C519 Patient1        KM        2        2013-02-21        2013-02-21        C519 Patient1        KM        3        2013-02-21        2013-04-21        C339 Patient1        BM        3        2013-04-21        2013-04-21        C339 Patient1        BM        4        2013-04-21        2013-05-13        C448 Patient1        AE        4        2013-05-13        2013-05-14        C448 Patient1        LC        4        2013-05-14        2013-05-14        C448 Patient1        AE        5        2013-05-14        2013-05-14        C436 Patient1        LC        5        2013-05-14        2013-05-23        C436```
as you can see, there are some extra lines, and this is because you've used the same datetime for the end of one ward stay and the start of another

you may want to tweak the "greater than or equal" and/or "less than or equal" to be strict inequalities, and see what that does

if you want to test it yourself, here is the sample data in DDL form --
Code:

```CREATE TABLE wardstays ( patientid    VARCHAR(9) , wardcode      CHAR(2) , startdatetime DATETIME , enddatetime  DATETIME ); INSERT INTO wardstays VALUES  ('Patient1','AM','2013-02-15 07:00:00','2013-02-17 07:00:00') ,('Patient1','SR','2013-02-17 07:00:00','2013-02-21 07:00:00') ,('Patient1','KM','2013-02-21 07:00:00','2013-04-21 07:00:00') ,('Patient1','BM','2013-04-21 08:00:00','2013-05-13 07:00:00') ,('Patient1','AE','2013-05-13 08:00:00','2013-05-14 07:00:00') ,('Patient1','LC','2013-05-14 07:00:00','2013-05-23 13:43:00') ; CREATE TABLE episodes ( patientid      VARCHAR(9) , episodenumber  SMALLINT , startdatetime  DATETIME , enddatetime    DATETIME , consultantcode VARCHAR(9) ); INSERT INTO episodes VALUES  ('Patient1',1,'2013-02-15 07:00:00','2013-02-17 08:00:00','C614') ,('Patient1',2,'2013-02-17 08:00:00','2013-02-21 08:00:00','C519') ,('Patient1',3,'2013-02-21 08:00:00','2013-04-21 09:00:00','C339') ,('Patient1',4,'2013-04-21 09:00:00','2013-05-14 07:00:00','C448') ,('Patient1',5,'2013-05-14 07:00:00','2013-05-23 13:43:00','C436') ;```
note also that GREATEST and LEAST are mysql functions, so if you're not using mysql, you'll want to use a CASE expression for each