gistfile1.sql

select distinct affiliateddr.provider_id, affiliateddr.provider_name_first, affiliateddr.provider_name_last,
                affiliateddr.primary_addr_line_1, affiliateddr.primary_addr_line_2, affiliateddr.primary_addr_city,
                affiliateddr.primary_addr_realm, affiliateddr.primary_addr_zip, affiliateddr.provider_default_score as iv_score,
                affiliateddr.provider_phone, affiliateddr.provider_fax, affiliateddr.provider_email,
                affiliateddr.provider_profile_selected as leader, affiliateddr.provider_profile_respondent as respondent, 
                affiliateddr.provider_profile_media as author, affiliateddr.provider_profile_clinical_trial as clinical_trial,
                os.org_site_id, o.org_name, os.org_site_addr_line_1, os.org_site_addr_line_2, 
                os.org_site_city, os.org_site_realm, os.org_site_zip, guy.provider_id as buddyid,
                guy.provider_name_first as guy_first, guy.provider_name_last as guy_last, guy.primary_addr_line_1 as guy_line_1, guy.primary_addr_line_2 as guy_line_2, 
                guy.primary_addr_line_3 as guy_line_3, guy.primary_addr_city as guy_city, guy.primary_addr_realm as guy_state, guy.primary_addr_zip as guy_zip,
                guy.provider_phone as guy_phone, guy.provider_fax as guy_fax, guy.provider_email as guy_email,affiliateddr.provider_default_score as buddy_iv_score,
                guy.provider_profile_selected as guy_leader, guy.provider_profile_respondent as guy_respondent, 
                guy.provider_profile_media as guy_author, guy.provider_profile_clinical_trial as guy_clinical_trial
from organization o
     inner join org_site os on (os.org_id = o.org_id)
     inner join provider_org_site dros on (dros.org_site_id = os.org_site_id)
     inner join provider affiliateddr on (affiliateddr.provider_id = dros.provider_id)
     inner join mip_dw.dbo.provider_nwork_degree_rltn_attr attr on (attr.s_provider_id = affiliateddr.provider_id) 
     inner join provider guy on (guy.provider_id = attr.t_provider_id)
where attr.roi_campaign_id = 38 and affiliateddr.project_id = 37 and o.org_id in (
1568,
1517,
4999,
1730,
5740,
22813,
4555,
5027,
8776,
1535,
5748,
23954,
23968,
24073,
1454,
20864,
7198,
22850,
24216,
17428,
11468,
12392,
20867,
21082,
4925,
20866,
5283,
1662,
24072,
4554,
682,
487,
4997,
4092,
21598,
4845,
18010,
6777,
17961,
1454,
4695,
1546,
6643,
7740,
8690,
9,
5789,
1641,
550,
5802,
21084) and attr.rltn_attr_id in (1,2,4,5) 
order by o.org_name, affiliateddr.provider_default_score desc