providerOrgNotLoaded

providerOrgNotLoaded

use mip_portal
go

select mip.provider_id, hp.[S No], hp.NPI, hp.[First Name], hp.[Last Name], hp.[Address]
      ,hp.City, hp.ST, mip.primary_addr_zip, hm.SITEID, hm.org_site_id, hm.MATCH_LEVEL
from stage.dbo.hds_provider hp
inner join (select distinct pid.provider_identifier_identity,p.provider_id, p.primary_addr_zip
			from provider p
			inner join provider_identifier pid on pid.provider_id = p.provider_id
			where p.project_id = 51
			  and pid.identifier_type_id = 22
			  and p.provider_id not in (select provider_id
										from provider_org_site)) as mip on mip.provider_identifier_identity = hp.NPI
left outer join stage.dbo.hds_masterOrg hm on hm.SITEID = hp.HDS_SiteID
order by 1