SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jul 2013
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jul 2013
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    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.

  4. #4
    SitePoint Member
    Join Date
    Jul 2013
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •