gistfile1.txt

select top 5000 p.provider_id
      ,p.provider_name_first, p.provider_name_last
      ,p.primary_addr_city, p.primary_addr_realm, p.primary_addr_zip
      ,round(p.provider_default_score*100,0) as iv_score
      ,round(msl.stat_rank*100,0) as msl_score
      ,'nationalTag' = case
       when nt.tagged is null then 'No'
       else 'Yes'
       end
from provider as p
left join (select provider_id, stat_rank
           from roi_campaign_provider_stat
           where roi_campaign_score_id = 43) as msl on msl.provider_id = p.provider_id
left join (select distinct provider_id, 'nationalTag' as tagged
           from vwNationalTag) as nt on nt.provider_id = p.provider_id
where p.project_id = 38
order by p.provider_default_score desc

select top 5000 p.provider_id
,p.provider_name_first, p.provider_name_last
,p.primary_addr_city, p.primary_addr_realm, p.primary_addr_zip
,round(p.provider_default_score_100,0) as iv_score
,round(msl.stat_rank_100,0) as msl_score
,nt.tag_ct
from provider as p
left join (select provider_id, stat_rank
from roi_campaign_provider_stat
where roi_campaign_score_id = 43) as msl on msl.provider_id = p.provider_id
left join (select provider_id, count(*) as tag_ct
from vwNationalTag
group by provider_id) as nt on nt.provider_id = p.provider_id
where p.project_id = 38
order by p.provider_default_score desc