Question

Locked

Subquery Needed?

By lacwill ·
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.

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

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

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."
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


Additional Comments
"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.

Questions
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)?

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

For a reasoned answer to such a query

by marv732 In reply to Subquery Needed?

you would need to either talk to one of your developers who may understand the data and its structure better or provide people with some information on which to base their answer.

At least you will need to provide
1. create table statements with appropriate primary , foreign keys and constraints
2. some sample generic data to populate the tables with

At the moment all we have is a sql query hanging in mid-air.
Remember, we don't all work in the same place or with the same products or tools so give us something to work with and, hopefully, someone will be able to help.

Greg

Back to Web Development Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums