SELECT     
	dbo.EFO_Campaign.Group_Name
	, dbo.order_profit.campaign_id 
FROM         
	dbo.order_profit 
	INNER JOIN  dbo.EFO_Campaign 
	ON dbo.order_profit.campaign_id = dbo.EFO_Campaign.Campaign_ID 
	INNER JOIN (	SELECT
				cs.campaign_id
				, SUM( cs.diff ) diff
				, SUM( cs.sum_check ) sum_check
				, SUM( cs.sum_profit ) sum_profit
			FROM	(
				SELECT
					op.campaign_id  as campaign_id
					, SUM( op.total_profit ) - ISNULL( cp.check_amount, 0 ) AS diff 
					, ISNULL( cp.check_amount, 0 ) sum_check
					, SUM( op.total_profit ) sum_profit
					, cp.check_id
					, c.is_over
				FROM	dbo.order_profit op
					LEFT OUTER JOIN dbo.check_period cp
						ON op.check_id = cp.check_id
					left join efo_campaign c
					on c.campaign_id = op.campaign_id
				GROUP BY 
					op.campaign_id
					, cp.organizer_id
					, cp.check_id
					, cp.check_amount
					, c.is_over
			) cs
			
			GROUP BY 
				cs.campaign_id
			HAVING
			( 	SUM( cs.diff ) >= 20 
			
			 OR	cs.campaign_id IN 
				(
					SELECT MAX(op.campaign_id)
					FROM	order_profit op
						left outer JOIN efo_campaign c
							ON op.campaign_id = c.campaign_id 
						LEFT OUTER JOIN check_period cp
							ON c.organizer_id = cp.organizer_id 
					WHERE
						cp.organizer_id IS NULL
					 AND	op.order_date >= '05/01/2004'
					GROUP BY op.campaign_id
				)	
			)
			OR
				( SUM( cs.diff ) >=4   and max(cast (cs.is_over as tinyint ))=1 )
			) V
	ON dbo.EFO_Campaign.Campaign_ID = v.campaign_id 
GROUP BY 
	dbo.order_profit.partner_id
	, dbo.EFO_Campaign.Group_Name
	, dbo.order_profit.campaign_id  
order by 
	dbo.order_profit.Campaign_ID