Subquery - TSQL 1

[MwTV]

Using the sql script displayed below in SQl Query Analyzer to extract the primary care physician (PCP) and the physician that provided the services to the patient.

If the PCP has a tax id of ‘3006562’, then the PCP has a CV_Physician medical group.

The provider id for the PCP is stored in the field “PROVIDER_ID” in the table titled “PROVIDER_RESET.”

The provider id for the physician that provided the services is stored in the field “PROV_ID” in the table titled “MEMBER_SOURCE.”

How do I definitely “pull” the pti.PROVIDER_TAX_ID for the primary care physician not the physician who provided the service?[b/]



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)”


Select
t.MEMBER_NBR
, t.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 b.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'

where

t.MEMBER_NBR in ('2563896','3628974')
AND pti.PROVIDER_TAX_ID = (select pti.PROVIDER_TAX_ID WHERE pr.PROVIDER_ID = pti.PROVIDER_ID)

group by

t.MEMBER_NBR
, pr.PROVIDER_ID
, pr.PROV_RELATION_PROV_ID
, pti.PROVIDER_TAX_ID
, pr.PROV_RELATION_PROV_ID
, g.PROV_FULL_NM


Thanks in advance.

 

[fuzzyocelot]

For clarification, what is the pr.PROV_RELATION_PROV_ID used for in the PROVIDER_RESET table? You have it in your query as "PCPProvId". Should this be pr.PROVIDER_ID instead? Also, why is it listed twice in your group by clause?

Also, in the select statement you're showing "b.CLM_LINE_SVC_PROV_ID". Should it be "a." instead of "b."? I'm just making sure there isn't another table involved that's not listed.

 

[fuzzyocelot]

Also, could you provide some examples of the records you have in the tables and the result you're currently getting? I tried to set up the situation you've described but I keep getting the PCP's tax id number which is what I think you want. So I'm not sure I understand what problem you're having.

 

[MwTV]

pr.PROV_RELATION_PROV_ID" is the provider_id for the primary care physician

Within the sql script, the section "t.PROV_ID as SvcProviderID" within the SELECT clause should have been "pr.PROV_ID"

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.

You are correct, "b.CLM_LINE_SVC_PROV_ID" should have been displayed as "a.CLM_LINE_SVC_PROV_ID."



Currently, the query result set is as follows;

SrcSysMemNbr--SvcProvID--PCPProvID---PhysCVPhys--ProvFullNm
2563896-------5015-------48956-------NOT_CV_PHYS-John Doe
2563896-------5015-------49055-------NOT_CV_PHYS-John Doe
2563896-------5283-------46523-------NOT_CV_PHYS-Sally Fa
2563896-------5246-------46526-------NOT_CV_PHYS-Bill Sne

Upon reviewing the query result set, 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."

Currently, I am exploring the use of subqueries and altering the joins.

Any insight as to what I am missing?

 

[fuzzyocelot]

Hi, I have another question for clarification...

In the original SQL statement, you have...

inner join PROVIDER_RESET pr on g.PROVIDER_ID = pr.PROVIDER_ID

Then you said...

Within the sql script, the section "t.PROV_ID as SvcProviderID" within the SELECT clause should have been "pr.PROV_ID"

So does the PROVIDER_RESET table have a PROV_ID and a PROVIDER_ID?

Thanks!

 

[MwTV]

Sorry for the mistake!

The PROVIDER_RESET table have "PROVIDER_ID" not "PROV_ID."

 

[fuzzyocelot]

Not a problem. I’m sorry to leave you hanging, but I haven’t had a chance to look at your problem again and I’m going to be gone next week. The place I’m going won’t have Internet access so I won’t be able to look at it. I’m really sorry.

The only thing I can suggest at this time is to try a basic query and then slowly build on it to see your results. Meaning, add a join, run it, analyze the results, add another join, repeat. I’m really sorry I can’t help much more than that right now. I hope you figure it out!

 

[MwTV]

Currently,

I am reviewing the query and have broken it into several parts instead of trying to do everything at one time.

My gut feeling tells me that this requires a subquery and the use of a "UNION."