SQL MAX query using 4 tables

Question

I'm having difficulty getting information from an SQL database.

The tables I am working with look something like this:


    Table: system.episode_history
    +------------+----------------+------------+----------------+
    | patient_id | episode_number | admit_date | discharge_date |
    +------------+----------------+------------+----------------+
    | 111        | 4              | 01/05/2017 |                | 
    +------------+----------------+------------+----------------+
    | 222        | 8              | 03/17/2017 |                | 
    +------------+----------------+------------+----------------+
    | 222        | 9              | 03/20/2017 |                | 
    +------------+----------------+------------+----------------+
    | 333        | 2              | 10/08/2017 |                |
    +------------+----------------+------------+----------------+
    | 444        | 7              | 08/09/2017 | 08/20/2017     |
    +------------+----------------+------------+----------------+

    Table: system.view_episode_summary_current
    +------------+----------------+------------+----------------------+
    | patient_id | episode_number | admit_date | last_date_of_service |
    +------------+----------------+------------+----------------------+
    | 111        | 4              | 01/05/2017 |  11/01/2017          | 
    +------------+----------------+------------+----------------------+
    | 222        | 8              | 03/17/2017 |  11/03/2017          | 
    +------------+----------------+------------+----------------------+
    | 222        | 9              | 03/20/2017 |  11/04/2017          | 
    +------------+----------------+------------+----------------------+
    | 333        | 2              | 10/08/2017 |  11/05/2017          |
    +------------+----------------+------------+----------------------+

    Table: system.history_attending_practitioner
    +------------+----------------+---------------------+-----------------------+
    | patient_id | episode_number | attend_practitioner | pract_assignment_date |
    +------------+----------------+---------------------+-----------------------+
    | 111        | 4              | 4444                | 01/05/2017            |
    +------------+----------------+---------------------+-----------------------+
    | 222        | 8              | 5555                | 03/17/2017            |
    +------------+----------------+---------------------+-----------------------+
    | 222        | 8              | 6666                | 03/20/2017            |
    +------------+----------------+---------------------+-----------------------+
    | 222        | 9              | 7777                | 04/10/2017            |
    +------------+----------------+---------------------+-----------------------+
    | 333        | 2              | 5555                | 10/08/2017            |
    +------------+----------------+---------------------+-----------------------+
    | 444        | 7              | 7777                | 08/09/2017            |
    +------------+----------------+---------------------+-----------------------+

    Table: system.user_practitioner_assignment
    +------------+----------------+---------------------+--------------------+
    | patient_id | episode_number | backup_practitioner | date_of_assignment |
    +------------+----------------+---------------------+--------------------+
    | 111        | 4              |                     |                    |
    +------------+----------------+---------------------+--------------------+
    | 222        | 8              | 7777                | 03/17/2017         |
    +------------+----------------+---------------------+--------------------+
    | 222        | 8              | 4444                | 05/18/2017         |
    +------------+----------------+---------------------+--------------------+
    | 222        | 9              |                     |                    |
    +------------+----------------+---------------------+--------------------+
    | 333        | 2              | 4444                | 10/08/2017         |
    +------------+----------------+---------------------+--------------------+
    | 333        | 2              | 5555                | 10/19/2017         |
    +------------+----------------+---------------------+--------------------+

I need the SQL query to return a table like this:


    +-------------+------------+----------------+------------+--------------------+----------------------+
    | {?Staff_ID} | patient_id | episode_number | admit_date | date_of_assignment | last_date_of_service |
    +-------------+------------+----------------+------------+--------------------+----------------------+
    | 4444        | 111        | 4              | 01/05/2017 | 01/05/2017         |  11/01/2017          |
    +-------------+------------+----------------+------------+--------------------+----------------------+
    | 4444        | 222        | 8              | 03/17/2017 | 05/18/2017         |  11/03/2017          |
    +-------------+------------+----------------+------------+--------------------+----------------------+

I'm using the system.episode_history table to determine if there is a discharge date connected to the episode.

WHERE (system.episode_history.discharge_date IS NULL)

I'm using the system.view_episode_summary_current table to get the last date of service connected to the episode.

I've tried several things, but without success.

First, the SQL handler seems to be fickle about the aliases in the query. I haven't figured out how to appease it yet.

Second, The SQL handle appears to be old enough that it doesn't know what to do with SQL window functions such as OVER, LAG(), LEAD(), etc. So I'm left with figuring out how to use self joins along with the MAX() function. I think this is an older SAP SQL server.

I'm brand new to SQL. For example, I'm assuming that capitalization in the SQL query is (mostly) irrelevant, based on what I've observed, but I'm not sure. I have been scouring the internet for help, but haven't found anything helpful that I can make work or make sense of (most of it feels like a foreign language at this point). Those I know both inside and outside of work that know SQL are stumped but this. I'm way out of my depth here, so any help would be appreciated.

UPDATE 1

I tweaked the suggested query below until I got no errors, and ended up with a query that looks like this:

select staff.staff_id, epi.patient_id, epi.episode_number as episode_id, epi.admit_date, staff.date_of_assignment, curr.last_date_of_service
from system.episode_history as epi
join (
    select patient_id, episode_number, attend_practitioner as staff_id, pract_assignment_date as date_of_assignment 
    from system.history_attending_practitioner
    union
    select patient_id, episode_number, backup_practitioner as staff_id, date_of_assignment as date_of_assignment 
    from system.user_practitioner_assignment
    where (backup_practitioner is not null)
    ) as staff on staff.patient_id=epi.patient_id and staff.episode_number=epi.episode_number
    and not exists(select * from system.history_attending_practitioner as a2 where a2.patient_id=staff.patient_id and a2.episode_number=staff.episode_number and a2.pract_assignment_date>staff.date_of_assignment)
    and not exists(select * from system.user_practitioner_assignment as a3 where a3.patient_id=staff.patient_id and a3.episode_number=staff.episode_number and a3.date_of_assignment>staff.date_of_assignment)
join system.view_episode_summary_current curr on curr.patient_id=epi.patient_id and curr.episode_number=e.episode_number
where (epi.discharge_date is null)
and staff.staff_id = '4444'

When I run this query against the real database, it correctly returns the 4 matching entries from system.history_attending_practitioner, but only returns 1 of the 8 matching entries from system.user_practitioner_assignment. At best, my numerous previous attempts only ever returned results from one table or the other but never both. So this is at least an improvement on that.

UPDATE 2

After tweaking the sequence of the query items, this is the version that returned the correct results:

select staff.staff_id, epi.patient_id, epi.episode_number as episode_id, epi.admit_date, staff.date_of_assignment, curr.last_date_of_service
from system.episode_history as epi
join (
    select patient_id, episode_number, attend_practitioner as staff_id, pract_assignment_date as date_of_assignment 
    from system.history_attending_practitioner
    where (attend_practitioner is not null)
    and not exists(select * from system.history_attending_practitioner as a2 where a2.patient_id=system.history_attending_practitioner.patient_id and a2.episode_number=system.history_attending_practitioner.episode_number and a2.pract_assignment_date>system.history_attending_practitioner.pract_assignment_date)
    union
    select patient_id, episode_number, backup_practitioner as staff_id, date_of_assignment as date_of_assignment 
    from system.user_practitioner_assignment
    where (backup_practitioner is not null)
    and not exists(select * from system.user_practitioner_assignment as a3 where a3.patient_id=system.user_practitioner_assignment.patient_id and a3.episode_number=system.user_practitioner_assignment.episode_number and a3.date_of_assignment>system.user_practitioner_assignment.date_of_assignment)
    ) as staff on staff.patient_id=epi.patient_id and staff.episode_number=epi.episode_number
join system.view_episode_summary_current curr on curr.patient_id=epi.patient_id and curr.episode_number=e.episode_number
where (epi.discharge_date is null)
and staff.staff_id = '4444'

This looks like a simple join between multiple tables, and a single max(last_date_of_service). Can you post your sql attempt. — TomC
See new comment below my answer. Need to post a standalone example that demonstrates the gap between this solution and your desired results. — TomC

TomC TomC · Accepted Answer · 2018-10-30T02:00:23

This is a bit hard to work out from your question. I thin you need to most recent assignment for each patient/episode.

You could do this with a second sub-query using max, or you could use not exists as per below.

As you mentioned, it would be much easier with a row_number() and or CTE's, but this is bulk standard sql version.

select s.staff_id, e.patient_id, e.episode_number as episode_id, e.admit_date, s.date_of_assignment, c.last_date_of_service
from episode_history e
join (
    select patient_id, episode_number, attend_practitioner as staff_id, pract_assignment_date as date_of_assignment 
    from history_attending_practitioner
    union
    select patient_id, episode_number, backup_practitioner as staff_id, date_of_assignment as date_of_assignment 
    from user_practitioner_assignment
    where backup_practitioner is not null
    ) s on s.patient_id=e.patient_id and s.episode_number=e.episode_number
    and not exists(select * from history_attending_practitioner a2 where a2.patient_id=s.patient_id and a2.episode_number=s.episode_number and a2.pract_assignment_date>s.date_of_assignment)
    and not exists(select * from user_practitioner_assignment a3 where a3.patient_id=s.patient_id and a3.episode_number=s.episode_number and a3.date_of_assignment>s.date_of_assignment)
join view_episode_summary_current c on c.patient_id=e.patient_id and c.episode_number=e.episode_number
where e.discharge_date is null

SQL MAX query using 4 tables

1 Answers