title: SQL MAX query using 3 tables
body: <p>I'm having difficulty querying an SQL database.</p>
  <pre><code>| 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            |
  
  | 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         |
  
  | patient_id | episode_number | admit_date | 
  | ---------- | -------------- | ---------- | 
  | 111        | 4              | 01/05/2017 |  
  | 222        | 8              | 03/17/2017 |  
  | 222        | 9              | 03/20/2017 |  
  | 333        | 2              | 10/08/2017 | 
  </code></pre>
  <p>I'm looking for an SQL query where I can enter a staff_id and then have it return all the open episodes they are currently assigned to. Result:</p>
  <pre><code>| staff_id | patient_id | episode_number | admit_date | date_of_assignment | 
  | -------- | ---------- | -------------- | ---------- | ------------------ | 
  | 4444     | 111        | 4              | 01/05/2017 | 01/05/2017         | 
  | 4444     | 222        | 8              | 03/17/2017 | 05/18/2017         | 
  </code></pre>
  <p>I don't understand how the SQL handles aliases in the query.</p>
  <p>The SQL doesn't know what to do with SQL window functions such as <code>OVER</code>, <code>LAG()</code>, <code>LEAD()</code>, etc. So I'm using self joins along with the <code>MAX()</code> function. Maybe this is an older SAP SQL server.</p>
  <p>I don't know whether capitalization in the SQL query is irrelevant.</p>