gistfile1.sql

-- attendees
SELECT rx_year, rx_month, product_name, SUM(nrx_cnt) AS rx_cnt
FROM provider_rx_segment prs
     INNER JOIN provider dr ON (dr.provider_id = prs.provider_id)
     INNER JOIN roi_campaign_project rcp ON (rcp.project_id = dr.project_id)
     INNER JOIN event_provider edr ON (edr.provider_id = dr.provider_id)
WHERE rcp.roi_campaign_id = 1 and edr.event_role_id = 1
GROUP BY rx_year, rx_month, product_name
order by rx_year, rx_month

-- counterparts
SELECT rx_year, rx_month, product_name, SUM(nrx_cnt) AS rx_cnt
FROM provider_rx_segment prs
     INNER JOIN provider dr ON (dr.counter_provider_id = prs.provider_id)
     INNER JOIN roi_campaign_project rcp ON (rcp.project_id = dr.project_id)
     INNER JOIN event_provider edr ON (edr.provider_id = dr.provider_id)
WHERE rcp.roi_campaign_id = 1 and edr.event_role_id = 1
GROUP BY rx_year, rx_month, product_name
order by rx_year, rx_month

-- stats
SELECT y, m, 
       SUM(x.is_influencer) AS influencers,
       SUM(on_target_list) AS target_listers,
       SUM(is_other) AS others,
       COUNT(DISTINCT event_id) AS evts
FROM (SELECT YEAR(e.event_start_time) AS y, MONTH(e.event_start_time) AS m,
		CASE WHEN provider_default_score > 0.0 THEN 1 ELSE 0 END AS is_influencer,
		CAST(ISNULL(drt.is_target_list, 0) AS INT) AS on_target_list,
		CASE WHEN (CASE WHEN provider_default_score > 0.0 THEN 1 ELSE 0 END) + ISNULL(drt.is_target_list, 0) >= 1 THEN 0 ELSE 1 END AS is_other,
		e.event_id
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 provider_provider_tag drdrt ON (drdrt.provider_id = p.provider_id)
	 LEFT OUTER JOIN provider_tag drt ON (drt.provider_tag_id = drdrt.provider_tag_id)
WHERE e.event_start_time BETWEEN rc.event_rx_start AND rc.event_rx_end
      AND ep.event_role_id = 1 AND rcp.roi_campaign_id = 1) x
GROUP BY y, m