gistfile1.txt

                
 SELECT x.y, x.m, 
                    SUM(x.is_influencer) AS influencer_count,
                    SUM(on_target_list) AS target_count,
                    SUM(is_other) AS other_count,
                    y.event_ct AS event_count
                FROM (SELECT DISTINCT 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,
		                     CASE WHEN provider_default_score = 0.0 THEN CAST(ISNULL(drt.is_target_list, 0) AS INT) ELSE 0 END 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, p.provider_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 geo_zip gz ON (gz.geo_zip_code = p.primary_addr_zip)
                           LEFT OUTER JOIN geo g ON (g.geo_id = gz.geo_id)
                           LEFT OUTER JOIN roi_campaign_geo_set rcgs ON (rcgs.geo_set_id = g.geo_set_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 = 46 AND (rcgs.roi_campaign_id = 46 OR rcgs.roi_campaign_id IS NULL)
                            AND (gz.geo_id = null OR null IS NULL)
                            AND e.event_id IN (SELECT DISTINCT ie.event_id
						    	               FROM event_provider iedr 
									  	            INNER JOIN [event] ie ON (ie.event_id = iedr.event_id)
											        INNER JOIN roi_campaign_project ircp ON (ircp.project_id = ie.project_id)
									           WHERE ircp.roi_campaign_id = 46 AND iedr.event_role_id = 2 AND (iedr.provider_id = null OR null IS NULL))) x
                    INNER JOIN (SELECT YEAR(event_start_time) AS y, MONTH(event_start_time) AS m, COUNT(event_id) as event_ct
				                FROM [event] e
				                     INNER JOIN event_location el ON (el.event_location_id = e.event_location_id)
				                     INNER JOIN roi_campaign_project rcp ON (rcp.project_id = e.project_id)
                                     INNER JOIN roi_campaign rc ON (rc.roi_campaign_id = rcp.roi_campaign_id)
                                     LEFT OUTER JOIN geo_zip gz ON (gz.geo_zip_code = el.event_location_zip)
                                     LEFT OUTER JOIN geo g ON (g.geo_id = gz.geo_id)
                                     LEFT OUTER JOIN roi_campaign_geo_set rcgs ON (rcgs.geo_set_id = g.geo_set_id)
				                WHERE e.event_start_time BETWEEN rc.event_rx_start AND rc.event_rx_end
										AND rcp.roi_campaign_id = 46 AND (rcgs.roi_campaign_id = 46 OR rcgs.roi_campaign_id IS NULL)
										AND (gz.geo_id = null OR null IS NULL)
										AND e.event_id IN (SELECT DISTINCT ie.event_id
						    							   FROM event_provider iedr 
									  							INNER JOIN [event] ie ON (ie.event_id = iedr.event_id)
																INNER JOIN roi_campaign_project ircp ON (ircp.project_id = ie.project_id)
														   WHERE ircp.roi_campaign_id = 46 AND iedr.event_role_id = 2 AND (iedr.provider_id = null OR null IS NULL))
								GROUP BY YEAR(event_start_time), MONTH(event_start_time)) y ON (y.m = x.m AND y.y = x.y)
                GROUP BY x.y, x.m,y.event_ct