event grouping

gistfile1.sql

SELECT MONTH(e.event_start_time) AS event_month, YEAR(e.event_start_time) AS event_year,
       SUM(ISNULL(influencer.is_influencer, 0)) AS is_influencer,
       SUM(CASE WHEN ISNULL(influencer.is_influencer, 0) = 1 AND ISNULL(target_list.on_target_list, 0) = 1 THEN 0 ELSE ISNULL(target_list.on_target_list, 0) END) AS on_target_list,
       SUM(CASE WHEN ISNULL(influencer.is_influencer, 0) + ISNULL(target_list.on_target_list, 0) >= 1 THEN 0 ELSE 1 END) AS is_other
FROM event_provider edr
     INNER JOIN provider dr ON (dr.provider_id = edr.provider_id)
     INNER JOIN roi_campaign_project rcp ON (rcp.project_id = dr.project_id)
     INNER JOIN [event] e ON (e.event_id = edr.event_id)
     LEFT OUTER JOIN (SELECT innerdr.provider_id, 1 AS is_influencer 
                      FROM provider innerdr 
                           INNER JOIN roi_campaign_project innerrcp ON (innerrcp.project_id = innerdr.project_id)
                      WHERE innerrcp.roi_campaign_id = 1 AND provider_default_score > 0) influencer ON (influencer.provider_id = dr.provider_id)
     LEFT OUTER JOIN (SELECT innerdr.provider_id, 1 AS on_target_list
                      FROM provider innerdr
                           INNER JOIN provider_provider_tag drdrt ON (drdrt.provider_id = innerdr.provider_id)
                           INNER JOIN provider_tag drt ON (drt.provider_tag_id = drdrt.provider_tag_id)
                           INNER JOIN roi_campaign_project innerrcp ON (innerrcp.project_id = innerdr.project_id)
                      WHERE innerrcp.roi_campaign_id = 1 AND drt.is_target_list = 1) target_list ON (target_list.provider_id = dr.provider_id)
WHERE rcp.roi_campaign_id = 1 and edr.event_role_id = 1
GROUP BY MONTH(e.event_start_time), YEAR(e.event_start_time)