Using the sql script below in SQL Query Analyzer to determine the member’s primary care physician, the physician who provided the service and the medical group (IPA) of the primary care physician.
, pr.PROV_ID as SvcProviderID
, pr.PROV_RELATION_PROV_ID as PCPProvID
,pti.PROVIDER_TAX_ID -- Is this the tax id of the PCP?
,case when PTI.PROVIDER_TAX_ID = '3006562' THEN 'CV_PHYS'
ELSE 'NOT_CV_PHYS' END AS PhysCVPHYS
, g.PROV_FULL_NM – Should be name of the medical group
From CLAIM a (NOLOCK)
inner join MEMBER_SOURCE t (NOLOCK) on a.MEMBER_ID = t.MEMBER_ID
and t.LOGICAL_DELETE_IND <> 'Y'
inner join PROVIDER g (NOLOCK) on a.CLM_LINE_SVC_PROV_ID = g.PROVIDER_ID
and g.LOGICAL_DELETE_IND <> 'Y'
inner join PROVIDER_RESET pr on g.PROVIDER_ID = pr.PROVIDER_ID
and pr.LOGICAL_DELETE_IND <> 'Y'
inner join PROVIDER_TAX_ID pti on g.PROVIDER_ID = PTI.PROVIDER_ID
AND pti.LOGICAL_DELETE_IND <> 'Y'
t.MEMBER_NBR in ('2563896','3628974')
AND pti.PROVIDER_TAX_ID = (select pti.PROVIDER_TAX_ID WHERE pr.PROVIDER_ID = pti.PROVIDER_ID)
Upon reviewing the query result set, as displayed below, it appears that I am extracting the name of the physician that provided the service not the medical group (IPA) of the primary care physician in the field titled "ProvFullNm." The contents of this field should be the name of the medical group (IPA)- something like "CV_Physician."
"pr.PROV_RELATION_PROV_ID" is the provider_id for the primary care physician
Note, "pr.PROV_ID" is the provider_id for the physician who provided the service. Further, the provider_id for the physician who provided the service and the provider_id for the primary care physician is stored in the same table, "PROVIDER_RESET."
Of course, sometimes the physician who provide service is also the primary care physician.
If the PCP has a tax id of ‘3006562’, then the PCP has a CV_Physician medical group.
How do I definitely “pull” the pti.PROVIDER_TAX_ID for the primary care physician not the physician who provided the service?
Then, I can determine if the primary care physician’s medical group is “CV_Physician using the CASE statement.
My initial try at this was to include a subquery in the WHERE clause such as
“AND pti.PROVIDER_TAX_ID = (select pti.PROVIDER_TAX_ID WHERE pr.PROVIDER_ID = pti.PROVIDER_ID)”
I'm having some trouble following what it is your looking for, as you seem to have multiple questions here. Not having your full table to look at don't know if I can offer valid fixes.
try adding another join to the provider table so that the provider table is joining to Prov_relation_prov_ID (from member table?) I'm assuming that the claim table only contains data on the rendering provider?
A join like that should also give you a link to determine the group for the primary care provider if your system is anything like ours.