Hound Hound - 1 year ago 66
SQL Question

Selecting ALL Max Values SQL query

I am working with two tables in this query

Table 1: admit
Table 2: Billing.

What I want to do is show people who are admitted to our crisis services
(program codes '44282' and '44283')
. For these people, I want to show their insurance, which is under the field
in the Billing Table. To do this, I need to show ALL the max coverage effective dates
where the coverage effective date is less than the admission date
and the coverage expiration date
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.

,max(A.cov_effective_date) as "MaxDate"



From System.view_episode_summary_admit as "SA"

Left Outer Join


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"
(A.patid = BG.patid) and (A.episode_number = BG.episode_number)

BG.cov_effective_date <= preadmit_admission_date and
(BG.cov_expiration_date >= preadmit_admission_date or
BG.cov_expiration_date Is Null)
) as "BGE"
(BGE.patid = SA.patid) and (BGE.episode_number = SA.episode_number)

(program_code = '44282' or program_code = '44283' )
and preadmit_admission_date >= {?Start Date}
and preadmit_admission_date <= {?End Date}
) A

Group By Patid, Episode_number

Answer Source

Sorry this is such a psuedo answer.

Select (your fields)
from (your entire query)bg
left join
   (select patid, max(cov_effective_date) maxdate from  system.billing_guar_emp_data group by patid) maxdate
on maxdate.patid = bg.patidate

Remove the group bys for the aggregate...you can now refer to maxdate.maxdate as a field in your opening select statement. Might be a better place to join this maxdate than joined at the very end of the query (possibly right under BG in the from statement), but psuedo code right? :) Hopefully you can apply the concept, let me know I'm free (freer?) in the afternoon if you need more.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download