arcaptaThreeOrgInfo.sql

use mip_portal
go

select distinct p.provider_id
      ,p.provider_name_first, p.provider_name_last
      ,p.primary_addr_line_1, p.primary_addr_line_2
      ,p.primary_addr_city, p.primary_addr_realm, p.primary_addr_zip
      ,p.provider_phone, p.provider_fax, p.provider_email
      ,p.provider_default_score as iv_score
      ,pct.ct_count
      ,pmi.pub_count
      ,nom.nom_count
      ,tag.provider_tag_name
      ,spec.specialty_name as primary_specialty
      ,org.org_site_name as primary_org_site_name
      ,org.org_site_city as primary_org_site_city
      ,org.org_site_realm as primary_org_site_realm
      ,org.org_site_zip as primary_org_site_zip
from provider as p
inner join provider_org_site as pos on pos.provider_id = p.provider_id
inner join org_site as os on os.org_site_id = pos.org_site_id
inner join organization as o on o.org_id = os.org_id
left outer join (select provider_id, count(clinical_trial_id) as ct_count
                 from provider_clinical_trial
                 group by provider_id
                ) as pct on pct.provider_id = p.provider_id
left outer join (select provider_id, count(media_item_id) as pub_count
                 from provider_media_item
                 group by provider_id
                ) as pmi on pmi.provider_id = p.provider_id
left outer join (select provider_id, count(distinct provider_survey_id) as nom_count
                 from response
                 group by provider_id
                ) as nom on nom.provider_id = p.provider_id
left outer join (select ppt.provider_id, pt.provider_tag_name
                 from provider_provider_tag as ppt
                 inner join provider_tag as pt on pt.provider_tag_id = ppt.provider_tag_id
                ) as tag on tag.provider_id = p.provider_id
left outer join (select ps.provider_id, s.specialty_name
                 from provider_specialty as ps
                 inner join specialty as s on s.specialty_id = ps.specialty_id
                 where ps.provider_specialty_primary = 1
                ) as spec on spec.provider_id = p.provider_id
left outer join (select pos.provider_id
                       ,os.org_site_name
                       ,os.org_site_city, os.org_site_realm, os.org_site_zip
                 from provider_org_site as pos
                 inner join org_site as os on os.org_site_id = pos.org_site_id
                 where pos.provider_org_site_primary = 1
                ) as org on org.provider_id = p.provider_id
where 1=1
  --and o.org_id in (3152,3238,4264)
  and (o.org_name like '%pfizer%' or o.org_name like '%Boehringer%' or o.org_name like '%Glaxo%')
  and p.project_id = 34