title: Selecting ALL Max Values SQL query
body: <p>I am working with two tables in this query <code>Table 1: admit</code>, <code>Table 2: Billing.</code>
  What I want to do is show people who are admitted to our crisis services <code>(program codes '44282' and '44283')</code>. For these people, I want to show their insurance, which is under the field <code>guarantor_id</code> in the Billing Table. To do this, I need to show ALL the max coverage effective dates <code>cov_effective_date</code> where the coverage effective date is less than the admission date <code>preadmit_admission_date</code> and the coverage expiration date <code>cov_expiration_date</code> is greater than the admission date (or Is Null).  The code I have right now does everything I want, but doesn’t get all the max coverage effective dates. So if someone had two different insurances that began on the same date it will only show one and I want it show both.</p>
  
  <pre><code>Select 
   A.patid
  ,A.episode_number
  ,A.preadmit_admission_date
  ,A.program_code
  ,A.program_value
  ,A.c_date_of_birth
  ,A.guarantor_id
  ,max(A.cov_effective_date) as "MaxDate"
  
  from(
  
  Select
    SA.patid
   ,SA.episode_number
   ,SA.preadmit_admission_date
   ,SA.program_code
   ,SA.program_value
   ,SA.c_date_of_birth
   ,BGE.guarantor_id
   ,BGE.cov_effective_date
  
   From System.view_episode_summary_admit as "SA"
  
   Left Outer Join
  
   (Select 
     BG.patid
    ,BG.episode_number
    ,BG.guarantor_id
    ,BG.cov_effective_date
    ,BG.cov_expiration_date
  
    from System.billing_guar_emp_data as "BG"
  
    Inner Join
  
    (Select patid, episode_number, preadmit_admission_date
    from System.view_episode_summary_admit ) as "A"
    On
    (A.patid = BG.patid) and (A.episode_number = BG.episode_number)
  
    Where
    BG.cov_effective_date &lt;= preadmit_admission_date and
   (BG.cov_expiration_date &gt;= preadmit_admission_date or
    BG.cov_expiration_date  Is Null)
    ) as "BGE"
    on
   (BGE.patid = SA.patid) and (BGE.episode_number = SA.episode_number)
  
    Where
    (program_code = '44282' or program_code = '44283' )
     and preadmit_admission_date &gt;= {?Start Date}
     and preadmit_admission_date &lt;= {?End Date}
     ) A
  
     Group By Patid, Episode_number
  </code></pre>