Help to Optimize this query

[Veejc]

I am trying to get just one record for each person who gets returned and a flag of either "Y" or "N" set on one column.

The Y or N value comes from this kind of logic:
Queries 2 tables. (Prof. claims and Inst. claims) I am looking for a specific code in both of those queries. (83655)

So, in the subquery, I join those tables and put a case statement to each of the separate tables saying that if I see this code (83655) then return "Y" otherwise return "N".

That works, it just takes a while.

Then in the outer query, I am trying to find a way to make sure that I ONLY return ONE record for each person, and I would like the Y or N values combined to one column to return a final Y or N value determining whether they had ANY instance of the 83655... but I don't need to know which table that value came from..

Just Y if 83655 is in either, or
N if 83655 does not appear at all in either table for that person on the record.

Does that make sense?

I believe the state that it's in right now will return 2 records for each person..

Name 83655 indicator
Jon Smith Y
Jon Smith N

I want to have

Jon Smith Y

John Smith


SELECT distinct CMD.AALASTNM as [Child's Last Name],
CMD.AAFNAME as [Child's First Name],
'' as [Child's Middle Initial],
CMD.aadob as [Child's Date of Birth],
CMD.aasex as [Child's Gender],
CMD.aaaddr1 as [Street Address],
cmd.aacity as [City/Town],
cmd.aastate as State,
cmd.aazip as Zip,
P.pashname as [Primary Care Provider],
'Parent Name' as [Parent/Guardian's Full Name],
'NHP' as [Health Plan],
ltrim(rtrim(cmd.aasubno)) + ltrim(rtrim(cmd.aapersno)) as [Policy Number],
CASE PC.cbproccode WHEN '83655' THEN 'Y' ELSE 'N' END as [P_CPT Code 83655 Indicator],
CASE IC.cbproccode WHEN '83655' THEN 'Y' ELSE 'N' END as [I_CPT Code 83655 Indicator],
'Y' as [Rite Care Member],
cmd.aassan as [Social Security],
cmd.aasubno as Subno,
cmd.aapersno as Persno,
convert(varchar, cmd.updat, 112) as [Date Modified]
FROM nhpri_diam_ds01.dbo.tbl_CurrentMemberDimension CMD
left outer JOIN nhpri_diam_ds01..tbl_professional_claims PC
ON CMD.aasubno = PC.CASUBNO
AND CMD.aapersno = PC.capersno
left outer JOIN nhpri_diam_ds01..tbl_institutional_claims IC
ON CMD.aasubno = IC.CASUBNO
AND CMD.aapersno = IC.capersno
-- INNER JOIN nhpri_diam_ds01..tbl_CurrentMemberDimension EG
-- ON CMD.aasubno = EG.aasubno
-- AND CMD.aapersno = EG.abpersno
LEFT JOIN nhpri_diam_ds01..jprovfm0_dat P
ON cmd.ABPCP = P.PAPROVID
WHERE year(CMD.aadob) = year( dateadd(yy, -3, getdate() ) )
-- between @startdate and @enddate --2 years old
AND CMD.aastate = 'RI' --Live in RI

--AND EG.abeffdt <= '20041231' AND (EG.abtermdt = '' or EG.abtermdt >= '20041231')
AND Left(cmd.abgrup,1) in ('1','5') --Medicaid Members Only From Eligibilty
and cmd.eligibility_flag = 1

 

[gmmastros]

This select should return a unique capersno from professional claims and institutional claims where the cbproccode = '83655'

Select  capersno
From	nhpri_diam_ds01..tbl_professional_claims
Where	cbproccode = '83655'

Union 

Select  capersno
From    nhpri_diam_ds01..tbl_institutional_claims
Where   cbproccode = '83655'

Meaning, if the person has a record in either table with an 83655 code, they will be included in the recordset returned.

Now, you use this as a subquery to join with other tables to return name, gender, etc....

Select Field1,
       Field2,
       Field3
From   Table
       Inner join [b](
          Select capersno
          From   nhpri_diam_ds01..tbl_professional_claims
          Where  cbproccode = '83655'

          Union 

          Select  capersno
          From    nhpri_diam_ds01..tbl_institutional_claims
          Where   cbproccode = '83655'
          )[/b] As [!]Code83655[/!]
          On Table.aapersno = [!]Code83655[/!].capersno
Where  aaState = 'RI'
Order By Whatever

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom