SQLTeam.com | Weblogs | Forums

Case statement in join clause


#1

SELECT *
FROM ##mi
WHERE MEASUREid='BP' and submeasure='TOT'
and plancode in ('QMX','QM') --these plancodes mapped to Reporting population='MEDCAD'
OR MEC2 LIKE '6757%' OR PayerCode = 'KD'

Tried below query.i dont think this is perfect way of writing ,eventhough its matching..
i have varous reporting population and based on the reporting population i need to join the columns

select dISTINCT a.*
from [##mi] AS A
left join qm.tbl_qsi_plan_to_RP(nolock) as b
on
a.[plan]=b.Market
and
(
(
Case ReportingPopulationName
when 'NCQA' then A.plancode
end
)=b.QNXTPlanCode

or

(Case ReportingPopulationName
when 'NCQA' then  A.PayerCode
end
)='KD'

or

(Case ReportingPopulationName
when 'NCQA' then  a.MEC2
end
) LIKE '6757%'

)
WHERE A.[PLAN]='MI' AND MEASUREid='BP' and submeasure='TOT' AND B.Market IS NOT NULL


#2
left join qm.tbl_qsi_plan_to_RP(nolock) as b
on
a.[plan]=b.Market
and
(ReportingPopulationName = 'NCQA' and
    ((A.plancode = b.QNXTPlanCode) or
     (A.PayerCode ='KD') or
     (a.MEC2 LIKE '6757%')))

#3

Hi Scott,
thanks for your Quick reply.

I have like more than 20 Reporting population. based on the reporting population no of columns in join varies.
Example :
if Reporting populationname='NCQA' then
(( A.plancode=b.QNXTPlanCode) or
(A.PayerCode ='KD') or
(a.MEC2 LIKE '6757%')))

if Reporting poulationame='GR' Then
(( A.plancode=b.QNXTPlanCode))

if Reporting poulationame='TT' Then
(( A.plancode=b.QNXTPlanCode))
OR MEC2 LIKE 'TK%'


#4

Adding to @ScottPletcher

left join qm.tbl_qsi_plan_to_RP(nolock) as b
on
a.[plan]=b.Market
and
(ReportingPopulationName = 'NCQA' and
    ((A.plancode = b.QNXTPlanCode) or
     (A.PayerCode ='KD') or
     (a.MEC2 LIKE '6757%'))
) OR

(ReportingPopulationName = 'GR' and A.plancode = b.QNXTPlanCode) OR

(ReportingPopulationName = 'TT' and 
  ((A.plancode = b.QNXTPlanCode) OR (MEC2 LIKE 'TK%'))
  )