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
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%')))
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%'
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%'))
)