LeadersBySurveyQuestionGeo - MD.sql

use mip_dw
go

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

SELECT drl.provider_name, drl.provider_id, drl.provider_name_last, drl.provider_name_first, drl.provider_name_middle,
  drl.provider_name_suffix, drl.provider_phone, drl.provider_email, drl.provider_addr_line_1, drl.provider_addr_line_2,
	drl.provider_addr_line_3, drl.provider_addr_city, drl.provider_addr_country, drl.provider_addr_realm, drl.provider_fax,
	drl.provider_addr_zip, drl.provider_profile_clinical_trial, drl.provider_profile_media, drl.provider_profile_selected, 
	drl.provider_profile_respondent, drl.provider_profile_event, drl.provider_addr_country, drl.provider_profile_campaign_ct, 
	drlc.provider_rank, drlc.provider_position, drlc.provider_percentile, prlg.geo_set_name, prlg.geo_name,
	COUNT(drl.provider_id) AS xxx_info_record_count
FROM provider_rank_lead drl
	INNER JOIN provider_rank_lead_class drlc ON (drlc.roi_campaign_id = drl.roi_campaign_id 
		AND drlc.provider_id = drl.provider_id)
	INNER JOIN provider_rank_lead_geo AS prlg ON (drlc.roi_campaign_id = drl.roi_campaign_id 
		AND drlc.provider_id = prlg.provider_id)
WHERE drlc.survey_question_id = @surveyQuestionId AND prlg.geo_set_id = @geoSetId AND geo_id IN (@geoId) 
	AND drl.roi_campaign_id = @roiCampaignId
GROUP BY drl.provider_name, drl.provider_id, drl.provider_name_last, drl.provider_name_first, drl.provider_name_middle,
	drl.provider_name_suffix, drl.provider_phone, drl.provider_email, drl.provider_addr_line_1, drl.provider_addr_line_2,
	drl.provider_addr_line_3, drl.provider_addr_city, drl.provider_addr_country, drl.provider_addr_realm, 
	drl.provider_addr_zip, drl.provider_profile_clinical_trial, drl.provider_profile_media, drl.provider_profile_selected, 
	drl.provider_profile_respondent, drl.provider_profile_event, drl.provider_addr_country, drl.provider_profile_campaign_ct, 
	drlc.provider_rank, drlc.provider_position, drlc.provider_percentile, prlg.geo_set_name, prlg.geo_name, drl.provider_fax
ORDER BY drlc.provider_rank DESC, drl.provider_name