jssn

gistfile1.txt

select dr.provider_id, dr.provider_name_last name_last, provider_name_first as name_first, 
       geo.geo_name as census_region, dr.primary_addr_line_1, dr.primary_addr_city, dr.primary_addr_realm as [state],
       dr.primary_addr_zip as zip, provider_email, 
       cana.ani_index, stat_iv.stat_rank as iv, stat_msl.stat_rank as msl,
       cana.rivermsl, cana.[rank], cana.imputeadv, cana.imputeprom,
       stat_msl.stat_custom_3 as rm_national, stat_msl.stat_custom_4 as rm_expert,
       dros.role_ct, ims.SPEC ims_specialty, ims.OVERALL_RATING as ims_rank,
       drdec.provider_decile_rank as dec_oad,
       case stat_iv.stat_custom_8 when 0 then '6. Non-Writer' 
                               when 1 then '5. Laggards'
                               when 2 then '4. Late Majority'
                               when 3 then '3. Early Majority'
                               when 4 then '2. Early Adopter'
                               when 5 then '1. Innovator'
                               else 'Unknown' end as ea,
	   drme.provider_identifier_identity as me_number, 
	   drims.provider_identifier_identity as ims_id,
	   drnpi.provider_identifier_identity as npi
from (select dr.provider_id, dr.provider_name_last, provider_name_first, 
             dr.primary_addr_line_1, dr.primary_addr_city, dr.primary_addr_realm,
             dr.primary_addr_zip, provider_email 
      from provider dr
		   inner join roi_campaign_provider_stat stat on (stat.provider_id = dr.provider_id)
	  where stat.roi_campaign_score_id = 37 and stat.stat_rank > 0
	  union 
	  select dr.provider_id, dr.provider_name_last, provider_name_first, 
	  	     dr.primary_addr_line_1, dr.primary_addr_city, dr.primary_addr_realm,
	  	     dr.primary_addr_zip , provider_email 
	  from provider dr
	 	   inner join roi_campaign_provider_stat stat on (stat.provider_id = dr.provider_id)
	  where stat.roi_campaign_score_id = 43 and stat.stat_rank > 0) dr
     left outer join (select dros.provider_id, count(provider_org_site_id) as role_ct
	                  from provider_org_site dros
	                       inner join provider_org_site_role drosr on (drosr.provider_org_site_role_id = dros.provider_org_site_role_id)
	                       inner join provider dr on (dr.provider_id = dros.provider_id)
	                  where project_id = 38 and dros.provider_org_site_role_id > 1 and provider_org_site_role_type_id = 1
	                  group by dros.provider_id) dros on (dros.provider_id = dr.provider_id)               
     left outer join roi_campaign_provider_stat stat_iv on (stat_iv.provider_id = dr.provider_id
                                                            and stat_iv.roi_campaign_score_id = 37)
     left outer join roi_campaign_provider_stat stat_msl on (stat_msl.provider_id = dr.provider_id
                                                             and stat_msl.roi_campaign_score_id = 41)
     left outer join mip_me.dbo.janssen_ani_cana cana on (cana.provider_id = dr.provider_id)
     left outer join provider_decile drdec on (drdec.provider_id = dr.provider_id)
     left outer join (select drid.provider_id, drid.provider_identifier_identity
                      from provider_identifier drid 
                           inner join provider dr on (dr.provider_id = drid.provider_id)
                      where dr.project_id = 38 and drid.identifier_type_id = 3) drims on (drims.provider_id = dr.provider_id)
     left outer join (select drid.provider_id, drid.provider_identifier_identity
                      from provider_identifier drid 
                           inner join provider dr on (dr.provider_id = drid.provider_id)
                      where dr.project_id = 38 and drid.identifier_type_id = 2) drme on (drme.provider_id = dr.provider_id)
     left outer join (select drid.provider_id, drid.provider_identifier_identity
                      from provider_identifier drid 
                           inner join provider dr on (dr.provider_id = drid.provider_id)
                      where dr.project_id = 38 and drid.identifier_type_id = 22) drnpi on (drnpi.provider_id = dr.provider_id)
     left outer join mip_dw.dbo.provider_profile_geo geo on (geo.provider_id = dr.provider_id and geo.geo_set_id = 126)
     left outer join stage.dbo.janssen_ea ims on (ims.imsid = drims.provider_identifier_identity)
order by stat_iv.stat_rank desc, stat_msl.stat_rank desc

use mip_portal
go

select dr.provider_id, dr.provider_name_last name_last, provider_name_first as name_first,
geo.geo_name as census_region, dr.primary_addr_line_1, dr.primary_addr_city, dr.primary_addr_realm as [state],
dr.primary_addr_zip as zip, provider_email,
cana.ani_index, stat_iv.stat_rank as iv, stat_msl.stat_rank as msl,
cana.rivermsl, cana.[rank], cana.imputeadv, cana.imputeprom,
stat_msl.stat_custom_3 as rm_national, stat_msl.stat_custom_4 as rm_expert,
dros.role_ct, ims.SPEC ims_specialty, ims.OVERALL_RATING as ims_rank,
drdec.provider_decile_rank as dec_oad,
jec.adoption_category,
drme.provider_identifier_identity as me_number,
drims.provider_identifier_identity as ims_id,
drnpi.provider_identifier_identity as npi
from (select dr.provider_id, dr.provider_name_last, provider_name_first,
dr.primary_addr_line_1, dr.primary_addr_city, dr.primary_addr_realm,
dr.primary_addr_zip, provider_email
from provider dr
inner join roi_campaign_provider_stat stat on (stat.provider_id = dr.provider_id)
where stat.roi_campaign_score_id = 37 and stat.stat_rank > 0
union
select dr.provider_id, dr.provider_name_last, provider_name_first,
dr.primary_addr_line_1, dr.primary_addr_city, dr.primary_addr_realm,
dr.primary_addr_zip , provider_email
from provider dr
inner join roi_campaign_provider_stat stat on (stat.provider_id = dr.provider_id)
where stat.roi_campaign_score_id = 43 and stat.stat_rank > 0) dr
left outer join (select dros.provider_id, count(provider_org_site_id) as role_ct
from provider_org_site dros
inner join provider_org_site_role drosr on (drosr.provider_org_site_role_id = dros.provider_org_site_role_id)
inner join provider dr on (dr.provider_id = dros.provider_id)
where project_id = 38 and dros.provider_org_site_role_id > 1 and provider_org_site_role_type_id = 1
group by dros.provider_id) dros on (dros.provider_id = dr.provider_id)
left outer join roi_campaign_provider_stat stat_iv on (stat_iv.provider_id = dr.provider_id
and stat_iv.roi_campaign_score_id = 37)
left outer join roi_campaign_provider_stat stat_msl on (stat_msl.provider_id = dr.provider_id
and stat_msl.roi_campaign_score_id = 41)
left outer join mip_me.dbo.janssen_ani_cana cana on (cana.provider_id = dr.provider_id)
left outer join provider_decile drdec on (drdec.provider_id = dr.provider_id)
left outer join (select drid.provider_id, drid.provider_identifier_identity
from provider_identifier drid
inner join provider dr on (dr.provider_id = drid.provider_id)
where dr.project_id = 38 and drid.identifier_type_id = 3) drims on (drims.provider_id = dr.provider_id)
left outer join (select drid.provider_id, drid.provider_identifier_identity
from provider_identifier drid
inner join provider dr on (dr.provider_id = drid.provider_id)
where dr.project_id = 38 and drid.identifier_type_id = 2) drme on (drme.provider_id = dr.provider_id)
left outer join (select drid.provider_id, drid.provider_identifier_identity
from provider_identifier drid
inner join provider dr on (dr.provider_id = drid.provider_id)
where dr.project_id = 38 and drid.identifier_type_id = 22) drnpi on (drnpi.provider_id = dr.provider_id)
left outer join mip_dw.dbo.provider_profile_geo geo on (geo.provider_id = dr.provider_id and geo.geo_set_id = 126)
left outer join stage.dbo.janssen_ea ims on (ims.imsid = drims.provider_identifier_identity)
left join mip_me.dbo.janssen_ea_category jec on jec.provider_id = dr.provider_id
where [RANK] != 0
order by stat_iv.stat_rank desc, stat_msl.stat_rank desc