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

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

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.

[table=“width: 500, class: grid”]
[tr]
[td]PatientID[/td]
[td]WardCode[/td]
[td]Ward Start Date Time[/td]
[td]Ward End Date Time[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]AM[/td]
[td]2013-02-15 07:00:00[/td]
[td]2013-02-17 07:00:00[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]SR[/td]
[td]2013-02-17 07:00:00[/td]
[td]2013-02-21 07:00:00[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]KM[/td]
[td]2013-02-21 07:00:00[/td]
[td]2013-04-21 07:00:00[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]BM[/td]
[td]2013-04-21 08:00:00[/td]
[td]2013-05-13 07:00:00[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]AE[/td]
[td]2013-05-13 08:00:00[/td]
[td]2013-05-14 07:00:00[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]LC[/td]
[td]2013-05-14 07:00:00[/td]
[td]2013-05-23 13:43:00[/td]
[/tr]
[/table]

Here is the episodes data:

[table=“width: 500, class: grid”]
[tr]
[td]PatientID[/td]
[td]Episode Number[/td]
[td]Episode Start Date Time[/td]
[td]Episode End Date Time[/td]
[td]Consultant Code[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]1[/td]
[td]2013-02-15 07:00:00[/td]
[td]2013-02-17 08:00:00[/td]
[td]C614[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]2[/td]
[td]2013-02-17 08:00:00[/td]
[td]2013-02-21 08:00:00[/td]
[td]C519[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]3[/td]
[td]2013-02-21 08:00:00[/td]
[td]2013-04-21 09:00:00[/td]
[td]C339[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]4[/td]
[td]2013-04-21 09:00:00[/td]
[td]2013-05-14 07:00:00[/td]
[td]C448[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]5[/td]
[td]2013-05-14 07:00:00[/td]
[td]2013-05-23 13:43:00[/td]
[td]C436[/td]
[/tr]
[/table]

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

[table=“width: 500, class: grid”]
[tr]
[td]PatientID[/td]
[td]Ward Code[/td]
[td]Episode Number[/td]
[td]Ward Consultant Start Date[/td]
[td]Ward Consultant End Date[/td]
[td]Consultant Code[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]AM[/td]
[td]1[/td]
[td]2013-02-15[/td]
[td]2013-02-17[/td]
[td]C614[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]SR[/td]
[td]1[/td]
[td]2013-02-17[/td]
[td]2013-02-17[/td]
[td]C614[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]SR[/td]
[td]2[/td]
[td]2013-02-17[/td]
[td]2013-02-21[/td]
[td]C519[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]KM[/td]
[td]2[/td]
[td]2013-02-21[/td]
[td]2013-02-21[/td]
[td]C519[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]KM[/td]
[td]3[/td]
[td]2013-02-21[/td]
[td]2013-04-21[/td]
[td]C339[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]BM[/td]
[td]3[/td]
[td]2013-04-21[/td]
[td]2013-04-21[/td]
[td]C339[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]BM[/td]
[td]4[/td]
[td]2013-04-21[/td]
[td]2013-05-13[/td]
[td]C448[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]AE[/td]
[td]4[/td]
[td]2013-05-13[/td]
[td]2013-05-14[/td]
[td]C448[/td]
[/tr]
[tr]
[td]Patient1[/td]
[td]LC[/td]
[td]5[/td]
[td]2013-05-14[/td]
[td]2013-05-14[/td]
[td]C436[/td]
[/tr]
[/table]

I hope you can advise.

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.

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.

okay, i used your test data and got pretty much exactly what you wanted

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 –


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