LeaderBySurveyQuestionGeo - MP.sql

use mip_portal
go

declare @roiCampaignId int = 1
declare @surveyQuestionId int = 8143
declare @geoId int = 397153
declare @geoSetId int = 137

select p.provider_id
      ,p.provider_name_last, p.provider_name_first, p.provider_name_middle, p.provider_name_suffix
      ,p.provider_name_last + ', ' + p.provider_name_first as provider_name
      ,p.provider_phone, p.provider_email, p.provider_fax
      ,p.primary_addr_line_1 as provider_addr_line_1, p.primary_addr_line_2 as provider_addr_line_2, p.primary_addr_line_3 as provider_addr_line_3
    ,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.primary_addr_country as provider_addr_country
	  ,p.provider_profile_clinical_trial, p.provider_profile_media, p.provider_profile_selected
	  ,p.provider_profile_respondent, p.provider_profile_event
	  ,p.provider_profile_campaign_ct
	  ,s.selection_ct as provider_rank
	  ,s.selection_ct as provider_position
	  ,1 - s.selection_ct/cast(max_provider_position as decimal(5,2)) as provider_percentile
	  ,g.geo_set_name, g.geo_name
	  ,count(distinct p.provider_id) as xxx_info_record_count, max_provider_position
from provider as p
join response as r on r.provider_id = p.provider_id
join survey_question as sq on sq.survey_question_id = r.survey_question_id
join roi_campaign_project as rcp on rcp.project_id = p.project_id

join (select rcp.roi_campaign_id
            ,p.provider_id
            ,count(distinct r.provider_survey_id) as selection_ct
      from roi_campaign_project as rcp
      join provider as p on p.project_id = rcp.project_id
      join response as r on r.provider_id = p.provider_id
      where rcp.roi_campaign_id = @roiCampaignId
      and r.survey_question_id = @surveyQuestionId
      group by rcp.roi_campaign_id, p.provider_id) as s on s.roi_campaign_id = rcp.roi_campaign_id
and s.provider_id = p.provider_id
--get max 'provider_position'
cross join (select x.roi_campaign_id, x.survey_question_id, max(x.provider_position) as max_provider_position
      from (select rcp.roi_campaign_id
                  ,sq.survey_question_id
                  ,count(r.provider_survey_id) as provider_position
            from response as r
            join survey_question as sq on sq.survey_question_id = r.survey_question_id
            join provider as p on p.provider_id = r.provider_id
            join roi_campaign_project as rcp on rcp.project_id = p.project_id
            where rcp.roi_campaign_id = @roiCampaignId
            and sq.survey_question_id = @surveyQuestionId
            group by rcp.roi_campaign_id, sq.survey_question_id, r.provider_survey_id) as x
      group by x.roi_campaign_id, x.survey_question_id) as position
join (select rcgs.roi_campaign_id
            ,gs.geo_set_name, gs.geo_set_id
            ,g.geo_name, g.geo_id
            ,gz.geo_zip_code
      from roi_campaign_geo_set as rcgs
      join geo_set as gs on gs.geo_set_id = rcgs.geo_set_id
      join geo as g on g.geo_set_id = gs.geo_set_id
      join geo_zip as gz on gz.geo_id = g.geo_id
      where rcgs.roi_campaign_id = @roiCampaignId
      and g.geo_set_id = @geoSetId
      and g.geo_id in (@geoId)) as g on g.roi_campaign_id = rcp.roi_campaign_id
and g.geo_zip_code = p.primary_addr_zip
where rcp.roi_campaign_id = @roiCampaignId
and sq.survey_question_id = @surveyQuestionId
group by p.provider_id, p.provider_name_last, p.provider_name_first, p.provider_name_middle, p.provider_name_suffix
        ,p.provider_phone, p.provider_email, p.provider_fax, p.primary_addr_line_1, p.primary_addr_line_2, p.primary_addr_line_3
	    ,p.primary_addr_city, p.primary_addr_realm, p.primary_addr_zip, p.primary_addr_country
	    ,p.provider_profile_clinical_trial, p.provider_profile_media, p.provider_profile_selected, max_provider_position, s.selection_ct
	    ,p.provider_profile_respondent, p.provider_profile_event, p.provider_profile_campaign_ct, g.geo_set_name, g.geo_name
ORDER BY provider_rank DESC, provider_name