Using dates to join records

[SolidMass]

I am a beginner to SQL and due to project time constraints, have been asked to help with the ETL testing on a new data warehouse (for a healthcare trust). Most of the SQL is simple but there is one part of the spec that is beyond my skill. There is a complex join I need to create between 2 tables using start and end dates to select the correct record. The tables are as follows:

Table 1 (Treatment)
record_key
system_code
patient_key
team_key
staff_key
treatment_date

Table 2 (Episode)
record_key
system_code
patient_key
team_key
staff_key
episode_start_date
episode_end_date
record_date

An individual patient may have multiple records in both tables. I need to select all records in the 'Treatment' table, and join this to the correct record on the 'Episode' table (if one exists) using the following criteria:

episode.system_code = treatment.system_code AND
episode.patient_key = treatment.patient_key AND
episode.team_key = treatment.team_key AND
episode.staff_key = treatment.staff_key AND
episode.episode_start_date <= treatment.treatment_date AND
episode.episode_end_date >= treatment.treatment_date

If more than one ‘Episode’ table record satisfies the criteria then select the record with:
MAX episode.episode_start_date THEN
MAX episode.record_date THEN
MAX episode.episode_end_date THEN
MAX episode.episode.record_key

I hope the above makes sense. I appreciate that this is a bit more than a standard question and I appreciate any help in writing this query or pointing me in the right direction.

Thanks.

 

[RiddleMeThis]

You need to do a subquery in the WHERE clause to select the record with the maximum date. Something like the following (change it around as needed but you should be able to see where im going with it):

SELECT *

FROM Treatment
RIGHT JOIN Episode
ON
episode.system_code = treatment.system_code
AND episode.patient_key = treatment.patient_key
AND episode.team_key = treatment.team_key
AND episode.staff_key = treatment.staff_key
AND episode.episode_start_date <= treatment.treatment_date
AND episode.episode_end_date >= treatment.treatment_date

WHERE
episode.episode_start_date =
(
SELECT *

FROM episode x

WHERE x.MAX(episode_start_date) = episode.episode_start_date

AND

episode.system_code = x.system_code
AND episode.patient_key = x.patient_key
AND episode.team_key = x.team_key
AND episode.staff_key = x.staff_key
)