influencerBySpe.sql

use mip_portal
go

declare @roiCampaignId int = 43
declare @geoSetId int = 147
declare @geoId int = 399359
declare @providerSpecialtyId int = 191

select distinct p.provider_id
      ,p.provider_name_first, p.provider_name_last, p.provider_name_middle
      ,p.provider_name_last + ', ' + p.provider_name_first as provider_name
      ,p.primary_addr_line_1 as provider_addr_line_1
      ,p.primary_addr_line_2 as provider_addr_line_2
      ,p.primary_addr_city as provider_addr_city
      ,p.primary_addr_realm as provider_addr_realm
      ,p.primary_addr_zip as provider_addr_zip
      ,p.provider_phone, p.provider_fax, p.provider_email
      ,ps.specialty_id as xxx_specialty_id
      ,s.specialty_name
      ,1 as xxx_info_record_count
      ,p.provider_profile_clinical_trial, p.provider_profile_event
      ,p.provider_profile_media, p.provider_profile_respondent
      ,p.provider_profile_selected
      ,p.provider_default_score as iv_score
from provider as p
join provider_specialty as ps on ps.provider_id = p.provider_id
join specialty as s on s.specialty_id = ps.specialty_id
join roi_campaign_project as rcp on rcp.project_id = p.project_id
join geo_zip as gz on gz.geo_zip_code = p.primary_addr_zip
join geo as g on g.geo_id = gz.geo_id
join geo_set as gs on gs.geo_set_id = g.geo_set_id
where rcp.roi_campaign_id = @roiCampaignId
--and gs.geo_set_id in (@geoSetId)
--and gz.geo_id in (@geoId)
--and ps.specialty_id in (@providerSpecialtyId)