Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    20

    Unanswered: Subquery Needed?

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


    Thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •