DROP PROCEDURE IF EXISTS sp_get_prospect_stats_for_city$
create procedure sp_get_prospect_stats_for_city
(
	IN in_city_id INT,
	IN in_campaign_id INT
)
BEGIN
	
	DECLARE total_p, notcalled INT;
	SET @called = 0;
	SET @notcalled = 0;
	
	SELECT count(r.residence_id)
		FROM residence r, location l
		WHERE r.location_id = l.location_id
		AND l.location_city_id = in_city_id
		INTO total_p;	
		
	SELECT count(r.residence_id)
		FROM residence r, location l
		WHERE r.location_id = l.location_id
		AND l.location_city_id = in_city_id
		AND r.residence_id NOT IN
			(
					SELECT cc.contacted_residence_id
					FROM campaign_contact cc
					WHERE cc.campaign_id=in_campaign_id				
			)
	INTO notcalled;
	
	SET @called = total_p - notcalled;
	SET @notcalled = notcalled;	
	
END$