Hound Hound - 4 months ago 12
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
guarantor_id
in the Billing Table. To do this, I need to show ALL the max coverage effective dates
cov_effective_date
where the coverage effective date is less than the admission date
preadmit_admission_date
and the coverage expiration date
cov_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.

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 <= preadmit_admission_date and
(BG.cov_expiration_date >= 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 >= {?Start Date}
and preadmit_admission_date <= {?End Date}
) A

Group By Patid, Episode_number

Answer

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.