SELECT 
CASE 
WHEN Member.MCTN_ID = Provider.MCTN_ID 
then 'Y' Else 'N'

FROM @claims c
INNER JOIN [FacetsReport].[dbo].CMC_CLCL_CLAIM clcl on c.CLCL_ID = clcl.CLCL_ID
INNER JOIN [FacetsReport].[dbo].CMC_CDML_CL_LINE cdml on clcl.CLCL_ID = cdml.CLCL_ID
--LEFT JOIN [FacetsReport].[dbo].CMC_CLCK_CLM_CHECK clck WITH (NOLOCK) on clcl.CLCL_ID = clck.CLCL_ID --Not all claims have entries in this table.
--INNER JOIN [FacetsReport].[dbo].CMC_PRPR_PROV paying_prpr WITH (NOLOCK) on clcl.CLCL_PAYEE_PR_ID = paying_prpr.PRPR_ID
INNER JOIN [FacetsReport].[dbo].CMC_PRPR_PROV ordering_prpr WITH (NOLOCK) on clcl.CLCL_PRPR_ID_REF = ordering_prpr.PRPR_ID
INNER JOIN [FacetsReport].[dbo].CMC_PRPR_PROV service_prpr WITH (NOLOCK) on clcl.PRPR_ID = service_prpr.PRPR_ID
INNER JOIN [FacetsReport].[dbo].CMC_PRAD_ADDRESS prad WITH (NOLOCK) on service_prpr.PRAD_ID  = prad.PRAD_ID and 
	service_prpr.PRAD_TYPE_PRIM = prad.PRAD_TYPE
	INNER JOIN [FacetsReport].[dbo].CMC_PRAD_ADDRESS proadd WITH (NOLOCK) on service_prpr.PRAD_ID  = proadd.PRAD_ID --and clcl.CLCL_LOW_SVC_DT between prad.PRAD_EFF_DT and prad.PRAD_TERM_DT
LEFT JOIN [FacetsReport].[dbo].CMC_CLHP_HOSP clhp WITH (NOLOCK) on clcl.CLCL_ID = clhp.CLCL_ID --Not all claims have entries in this table. 
--LEFT JOIN @facilityMapping facmap on substring(clhp.CLHP_FAC_TYPE,2,1)+clhp.CLHP_BILL_CLASS = facmap.fromCode --Not all claims have entries in this table.
INNER JOIN [FacetsReport].[dbo].[CMC_PDDS_PROD_DESC] pdds WITH (NOLOCK) on pdds.PDPD_ID = clcl.PDPD_ID
INNER JOIN [FacetsReport].[dbo].[CMC_CDCB_LI_COB] COB on clcl.CLCL_ID=COB.CLCL_ID
--LEFT JOIN @planTypeMapping planmap on clcl.PDDS_MCTR_BCAT = planmap.PDDS_MCTR_BCAT and (pdds.PDDS_MCTR_VAL2 = planmap.PDDS_MCTR_VAR2 or planmap.PDDS_MCTR_VAR2 = '')
INNER JOIN [FacetsReport].[dbo].CMC_SBSB_SUBSC sbsb WITH (NOLOCK) on clcl.SBSB_CK = sbsb.SBSB_CK
INNER JOIN [FacetsReport].[dbo].CMC_MEME_MEMBER meme WITH (NOLOCK) on clcl.MEME_CK = meme.MEME_CK
LEFT JOIN [FacetsReport].[dbo].[CMC_MEPR_PRIM_PROV]MEPR WITH (NOLOCK) on  meme.MEME_CK=--tring to implement the join
INNER JOIN @claimDiagnosisCodes diag on clcl.CLCL_ID = diag.CLCL_ID
LEFT JOIN @claimProcedureCodeUBSurgs surg on clcl.CLCL_ID = surg.CLCL_ID --Not all claims have entries in this table.LEFT JOIN @PresentonAdmissions poa on clcl.CLCL_ID = poa.CLCL_ID
LEFT JOIN @PresentonAdmissions poa on clcl.CLCL_ID = poa.CLCL_ID
LEFT JOIN [FacetsReport].[dbo].[CMC_CLMF_MULT_FUNC] clmf WITH (NOLOCK) on clcl.CLCL_ID = clmf.CLCL_ID
--LOGIC TO BE IMPLEMENTED "Where Member PCP TaxID = Provider Group TaxID then 'Y' Else 'N'_ WHICH i JAVE DONE THE SELECT ABOVE HOW DO i MPLEMENT THE join clause
--Member PCP TaxID is:
select CMC_PRPR_PROV.MCTN_ID
from CMC_MEPR_PRIM_PROV
join CMC_PRPR_PROV on CMC_PRPR_PROV.PRPR_ID = CMC_MEPR_PRIM_PROV.PRPR_ID
where CMC_MEPR_PRIM_PROV.MEME_CK = ######

--Provider Group TaxID is:
select CMC_PRPR_PROV.MCTN_ID
from CMC_PRER_RELATION
join CMC_PRPR_PROV on CMC_PRPR_PROV.PRPR_ID = CMC_PRER_RELATION.PRER_PRPR_ID
where CMC_PRER_RELATION.PRPR_ID = ############

Please describe more information about you requirement, only with the query it hard for others to understand your requirement.

What is your question ?

Is your question is how can you implement your business logic through T-SQL ,Is it ?

SELECT 
CASE 
WHEN Member.MCTN_ID = Provider.MCTN_ID 
then 'Y' Else 'N'

FROM @claims c
INNER JOIN [FacetsReport].[dbo].CMC_CLCL_CLAIM clcl on c.CLCL_ID = clcl.CLCL_ID
INNER JOIN [FacetsReport].[dbo].CMC_CDML_CL_LINE cdml on clcl.CLCL_ID = cdml.CLCL_ID
--LEFT JOIN [FacetsReport].[dbo].CMC_CLCK_CLM_CHECK clck WITH (NOLOCK) on clcl.CLCL_ID = clck.CLCL_ID --Not all claims have entries in this table.
--INNER JOIN [FacetsReport].[dbo].CMC_PRPR_PROV paying_prpr WITH (NOLOCK) on clcl.CLCL_PAYEE_PR_ID = paying_prpr.PRPR_ID
INNER JOIN [FacetsReport].[dbo].CMC_PRPR_PROV ordering_prpr WITH (NOLOCK) on clcl.CLCL_PRPR_ID_REF = ordering_prpr.PRPR_ID
INNER JOIN [FacetsReport].[dbo].CMC_PRPR_PROV service_prpr WITH (NOLOCK) on clcl.PRPR_ID = service_prpr.PRPR_ID
INNER JOIN [FacetsReport].[dbo].CMC_PRAD_ADDRESS prad WITH (NOLOCK) on service_prpr.PRAD_ID  = prad.PRAD_ID and 
	service_prpr.PRAD_TYPE_PRIM = prad.PRAD_TYPE
	INNER JOIN [FacetsReport].[dbo].CMC_PRAD_ADDRESS proadd WITH (NOLOCK) on service_prpr.PRAD_ID  = proadd.PRAD_ID --and clcl.CLCL_LOW_SVC_DT between prad.PRAD_EFF_DT and prad.PRAD_TERM_DT
LEFT JOIN [FacetsReport].[dbo].CMC_CLHP_HOSP clhp WITH (NOLOCK) on clcl.CLCL_ID = clhp.CLCL_ID --Not all claims have entries in this table. 
--LEFT JOIN @facilityMapping facmap on substring(clhp.CLHP_FAC_TYPE,2,1)+clhp.CLHP_BILL_CLASS = facmap.fromCode --Not all claims have entries in this table.
INNER JOIN [FacetsReport].[dbo].[CMC_PDDS_PROD_DESC] pdds WITH (NOLOCK) on pdds.PDPD_ID = clcl.PDPD_ID
INNER JOIN [FacetsReport].[dbo].[CMC_CDCB_LI_COB] COB on clcl.CLCL_ID=COB.CLCL_ID
--LEFT JOIN @planTypeMapping planmap on clcl.PDDS_MCTR_BCAT = planmap.PDDS_MCTR_BCAT and (pdds.PDDS_MCTR_VAL2 = planmap.PDDS_MCTR_VAR2 or planmap.PDDS_MCTR_VAR2 = '')
INNER JOIN [FacetsReport].[dbo].CMC_SBSB_SUBSC sbsb WITH (NOLOCK) on clcl.SBSB_CK = sbsb.SBSB_CK
INNER JOIN [FacetsReport].[dbo].CMC_MEME_MEMBER meme WITH (NOLOCK) on clcl.MEME_CK = meme.MEME_CK
LEFT JOIN [FacetsReport].[dbo].[CMC_MEPR_PRIM_PROV]MEPR WITH (NOLOCK) on  meme.MEME_CK=--tring to implement the join
INNER JOIN @claimDiagnosisCodes diag on clcl.CLCL_ID = diag.CLCL_ID
LEFT JOIN @claimProcedureCodeUBSurgs surg on clcl.CLCL_ID = surg.CLCL_ID --Not all claims have entries in this table.LEFT JOIN @PresentonAdmissions poa on clcl.CLCL_ID = poa.CLCL_ID
LEFT JOIN @PresentonAdmissions poa on clcl.CLCL_ID = poa.CLCL_ID
LEFT JOIN [FacetsReport].[dbo].[CMC_CLMF_MULT_FUNC] clmf WITH (NOLOCK) on clcl.CLCL_ID = clmf.CLCL_ID
--LOGIC TO BE IMPLEMENTED "Where Member PCP TaxID = Provider Group TaxID then 'Y' Else 'N'_ WHICH i JAVE DONE THE SELECT ABOVE HOW DO i MPLEMENT THE join clause
--Member PCP TaxID is:
select CMC_PRPR_PROV.MCTN_ID
from CMC_MEPR_PRIM_PROV
join CMC_PRPR_PROV on CMC_PRPR_PROV.PRPR_ID = CMC_MEPR_PRIM_PROV.PRPR_ID
where CMC_MEPR_PRIM_PROV.MEME_CK = ######

--Provider Group TaxID is:
select CMC_PRPR_PROV.MCTN_ID
from CMC_PRER_RELATION
join CMC_PRPR_PROV on CMC_PRPR_PROV.PRPR_ID = CMC_PRER_RELATION.PRER_PRPR_ID
where CMC_PRER_RELATION.PRPR_ID = ############

(select CMC_PRPR_PROV.PRPR_ID, CMC_PRPR_PROV.MCTN_ID
from CMC_MEPR_PRIM_PROV
join CMC_PRPR_PROV on CMC_PRPR_PROV.PRPR_ID = CMC_MEPR_PRIM_PROV.PRPR_ID) member (MEME_CK, MCTN_ID),

--Provider Group TaxID is:
(select CMC_PRER_RELATION.PRPR_ID, CMC_PRPR_PROV.MCTN_ID
from CMC_PRER_RELATION
join CMC_PRPR_PROV on CMC_PRPR_PROV.PRPR_ID = CMC_PRER_RELATION.PRER_PRPR_ID) provider (PRPR_ID, MCTN_ID)

where member.MEME_CK = clcl.MEME_CK
and provider.PRPR_ID = clcl.PRPR_ID

• Edited by Soumen Barua Monday, January 19, 2015 7:15 AM

yes that is the question

Does this solve it?

SELECT 
 CASE WHEN Member.MCTN_ID = Provider.MCTN_ID THEN 'Y' 
      ELSE 'N'
 END, *
  FROM @claims c
    INNER JOIN FacetsReport.dbo.CMC_CLCL_CLAIM clcl 
      ON c.CLCL_ID = clcl.CLCL_ID
    INNER JOIN FacetsReport.dbo.CMC_CDML_CL_LINE cdml 
      ON clcl.CLCL_ID = cdml.CLCL_ID
    INNER JOIN FacetsReport.dbo.CMC_PRPR_PROV ordering_prpr WITH (NOLOCK) 
      ON clcl.CLCL_PRPR_ID_REF = ordering_prpr.PRPR_ID
    INNER JOIN FacetsReport.dbo.CMC_PRPR_PROV service_prpr WITH (NOLOCK) 
      ON clcl.PRPR_ID = service_prpr.PRPR_ID
    INNER JOIN FacetsReport.dbo.CMC_PRAD_ADDRESS prad WITH (NOLOCK) 
      ON service_prpr.PRAD_ID = prad.PRAD_ID 
      AND service_prpr.PRAD_TYPE_PRIM = prad.PRAD_TYPE
    INNER JOIN FacetsReport.dbo.CMC_PRAD_ADDRESS proadd WITH (NOLOCK) 
      ON service_prpr.PRAD_ID = proadd.PRAD_ID 
    LEFT JOIN FacetsReport.dbo.CMC_CLHP_HOSP clhp WITH (NOLOCK) 
	  ON clcl.CLCL_ID = clhp.CLCL_ID 
    INNER JOIN FacetsReport.dbo.CMC_PDDS_PROD_DESC pdds WITH (NOLOCK) 
      ON pdds.PDPD_ID = clcl.PDPD_ID
    INNER JOIN FacetsReport.dbo.CMC_CDCB_LI_COB COB 
	  ON clcl.CLCL_ID=COB.CLCL_ID
    INNER JOIN FacetsReport.dbo.CMC_SBSB_SUBSC sbsb WITH (NOLOCK) 
      ON clcl.SBSB_CK = sbsb.SBSB_CK
    INNER JOIN FacetsReport.dbo.CMC_MEME_MEMBER meme WITH (NOLOCK) 
      ON clcl.MEME_CK = meme.MEME_CK
    INNER JOIN @claimDiagnosisCodes diag 
      ON clcl.CLCL_ID = diag.CLCL_ID
    LEFT JOIN @claimProcedureCodeUBSurgs surg 
      ON clcl.CLCL_ID = surg.CLCL_ID
	LEFT JOIN @PresentonAdmissions poa 
	  ON clcl.CLCL_ID = poa.CLCL_ID
    LEFT JOIN @PresentonAdmissions poa 
      ON clcl.CLCL_ID = poa.CLCL_ID
    LEFT JOIN FacetsReport.dbo.CMC_CLMF_MULT_FUNC clmf WITH (NOLOCK) 
      ON clcl.CLCL_ID = clmf.CLCL_ID
-------------------------------------------------------------------------------
    LEFT OUTER JOIN (
                     select CMC_PRPR_PROV.MCTN_ID
    				   from CMC_MEPR_PRIM_PROV cmpp
    				     INNER JOIN CMC_PRPR_PROV  cpp
    					   ON cpp.PRPR_ID = cmpp.PRPR_ID
    				) mem
      ON meme.MEME_CK = mem.MEME_CK
	LEFT OUTER JOIN (
	                 select CMC_PRPR_PROV.MCTN_ID
					   from CMC_PRER_RELATION cpr
					     INNER JOIN CMC_PRPR_PROV cpp
						   ON cpp.PRPR_ID = cpr.PRER_PRPR_ID
                    ) prov
      ON meme.MEM_CK = prov.PRPR_ID

• Proposed as answer by Charlie LiaoMicrosoft contingent staff, Moderator Tuesday, January 27, 2015 2:46 AM

Hi Dre,

Does Patrick's sample query helpful for you on this issue? If the issue persists, please post the detail information about your issue, so that we can make further analysis.

Regards,

Thanks all  for the response