gistfile1.txt

SELECT p.provider_id, provider_name_last, provider_name_first, ISNULL(provider_default_score, 0) AS iv_score, 
       ISNULL(speaker.stat_rank, 0) AS speaker_score, COUNT(e.event_id) AS event_cnt, 
       SUM(attendees.cnt) AS attendee_cnt
FROM provider p
     INNER JOIN event_provider ep ON (p.provider_id = ep.provider_id)
     INNER JOIN [event] e ON (e.event_id = ep.event_id)
     INNER JOIN roi_campaign_project rcp ON (rcp.project_id = p.project_id)
     INNER JOIN roi_campaign rc ON (rc.roi_campaign_id = rcp.roi_campaign_id)
     LEFT OUTER JOIN (SELECT event_id, COUNT(ep.provider_id) AS cnt
                      FROM event_provider ep
                           INNER JOIN provider dr ON (dr.provider_id = ep.provider_id)
                           INNER JOIN roi_campaign_project rcp ON (rcp.project_id = dr.project_id)
                      WHERE rcp.roi_campaign_id = 1 AND ep.event_role_id = 1
                      GROUP BY event_id) attendees ON (attendees.event_id = e.event_id)  
     LEFT OUTER JOIN (SELECT stat.provider_id, stat.stat_rank 
                      FROM roi_campaign_provider_stat stat
                           INNER JOIN roi_campaign_score rcs ON (rcs.roi_campaign_score_id = stat.roi_campaign_score_id)
                           INNER JOIN score s ON (s.score_id = rcs.score_id)
                      WHERE rcs.roi_campaign_id = 1 AND rcs.roi_campaign_score_type_id = 5) speaker ON (speaker.provider_id = p.provider_id)
WHERE rcp.roi_campaign_id = 1 AND e.event_start_time BETWEEN rc.event_rx_start AND rc.event_rx_end AND ep.event_role_id = 2 
GROUP BY p.provider_id, provider_name_last, provider_name_first, speaker.stat_rank, provider_default_score