Need help with using NOT IN in a Stored Procedure. I've tried various ways, but data still is incorrect. Maybe someone can tell me what I'm doing wrong. Thanks in advance for your help.
SELECT
ad_num = mc_ad_detail.ad_num
, source = mc_acct_master_view.source
, adv_type = mc_ad_detail.adv_type
, period_year = mc_period_dates.period_year
, prod_code = mc_ad_insertion.prod_code
, credit_amt = mc_ad_cost_view.credit_amt
, debit_amt = mc_ad_cost_view.debit_amt
, obj_code = mc_ad_cost_view.obj_code
, obj_code_sub = mc_ad_cost_view.obj_code_sub
, region = mc_acct_master_view.region
, region_desc = mc_region_vals.region_desc
, territory = mc_acct_master_view.territory
, insertion_date = Convert(VarChar(10), mc_ad_insertion.insertion_date, 101)
, effective_inches = mc_ad_insertion.effective_inches
, effective_lines =
CASE
WHEN mc_ad_cost_view.obj_code_sub = '1'
THEN mc_ad_insertion.effective_lines
ELSE 0
END
FROM mc_acct_master_view
INNER JOIN mc_ad_detail
ON (mc_acct_master_view.bus_unit_id = mc_ad_detail.bus_unit_id) AND
(mc_acct_master_view.pub_id = mc_ad_detail.pub_id) AND
(mc_acct_master_view.acct_num = mc_ad_detail.acct_num)
INNER JOIN mc_ad_insertion
ON (mc_ad_detail.bus_unit_id = mc_ad_insertion.bus_unit_id) AND
(mc_ad_detail.pub_id = mc_ad_insertion.pub_id) AND
(mc_ad_detail.acct_num = mc_ad_insertion.acct_num) AND
(mc_ad_detail.ad_num = mc_ad_insertion.ad_num) AND
(mc_ad_detail.entry_date_time = mc_ad_insertion.entry_date_time)
INNER JOIN mc_ad_cost_view
ON (mc_ad_insertion.bus_unit_id = mc_ad_cost_view.bus_unit_id) AND
(mc_ad_insertion.pub_id = mc_ad_cost_view.pub_id) AND
(mc_ad_insertion.acct_num = mc_ad_cost_view.acct_num) AND
(mc_ad_insertion.ad_num = mc_ad_cost_view.ad_num) AND
(mc_ad_insertion.insertion_num = mc_ad_cost_view.trans_id)
INNER JOIN mc_obj_code_vals
ON mc_ad_cost_view.obj_code = mc_obj_code_vals.obj_code
INNER JOIN mc_region_vals
ON mc_acct_master_view.region = mc_region_vals.region
--INNER JOIN mc_territory_vals
-- ON mc_acct_master_view.territory = mc_territory_vals.territory
INNER JOIN mc_period_dates
ON mc_ad_insertion.insertion_date = mc_period_dates.calendar_date
WHERE mc_ad_cost_view.obj_code NOT IN (select obj_code from mc_ad_cost_view where obj_code NOT IN ('19','29','900') and
mc_ad_insertion.prod_code NOT IN (SELECT prod_code FROM mc_ad_insertion WHERE prod_code NOT IN('ELDY','INEM')) AND
mc_ad_detail.adv_type IN ('CL','GC','CT','GT','GE') AND
mc_acct_master_view.region IN ('COMM','COOP''SHOW') AND
SELECT
ad_num = mc_ad_detail.ad_num
, source = mc_acct_master_view.source
, adv_type = mc_ad_detail.adv_type
, period_year = mc_period_dates.period_year
, prod_code = mc_ad_insertion.prod_code
, credit_amt = mc_ad_cost_view.credit_amt
, debit_amt = mc_ad_cost_view.debit_amt
, obj_code = mc_ad_cost_view.obj_code
, obj_code_sub = mc_ad_cost_view.obj_code_sub
, region = mc_acct_master_view.region
, region_desc = mc_region_vals.region_desc
, territory = mc_acct_master_view.territory
, insertion_date = Convert(VarChar(10), mc_ad_insertion.insertion_date, 101)
, effective_inches = mc_ad_insertion.effective_inches
, effective_lines =
CASE
WHEN mc_ad_cost_view.obj_code_sub = '1'
THEN mc_ad_insertion.effective_lines
ELSE 0
END
FROM mc_acct_master_view
INNER JOIN mc_ad_detail
ON (mc_acct_master_view.bus_unit_id = mc_ad_detail.bus_unit_id) AND
(mc_acct_master_view.pub_id = mc_ad_detail.pub_id) AND
(mc_acct_master_view.acct_num = mc_ad_detail.acct_num)
INNER JOIN mc_ad_insertion
ON (mc_ad_detail.bus_unit_id = mc_ad_insertion.bus_unit_id) AND
(mc_ad_detail.pub_id = mc_ad_insertion.pub_id) AND
(mc_ad_detail.acct_num = mc_ad_insertion.acct_num) AND
(mc_ad_detail.ad_num = mc_ad_insertion.ad_num) AND
(mc_ad_detail.entry_date_time = mc_ad_insertion.entry_date_time)
INNER JOIN mc_ad_cost_view
ON (mc_ad_insertion.bus_unit_id = mc_ad_cost_view.bus_unit_id) AND
(mc_ad_insertion.pub_id = mc_ad_cost_view.pub_id) AND
(mc_ad_insertion.acct_num = mc_ad_cost_view.acct_num) AND
(mc_ad_insertion.ad_num = mc_ad_cost_view.ad_num) AND
(mc_ad_insertion.insertion_num = mc_ad_cost_view.trans_id)
INNER JOIN mc_obj_code_vals
ON mc_ad_cost_view.obj_code = mc_obj_code_vals.obj_code
INNER JOIN mc_region_vals
ON mc_acct_master_view.region = mc_region_vals.region
--INNER JOIN mc_territory_vals
-- ON mc_acct_master_view.territory = mc_territory_vals.territory
INNER JOIN mc_period_dates
ON mc_ad_insertion.insertion_date = mc_period_dates.calendar_date
WHERE mc_ad_cost_view.obj_code NOT IN (select obj_code from mc_ad_cost_view where obj_code NOT IN ('19','29','900') and
mc_ad_insertion.prod_code NOT IN (SELECT prod_code FROM mc_ad_insertion WHERE prod_code NOT IN('ELDY','INEM')) AND
mc_ad_detail.adv_type IN ('CL','GC','CT','GT','GE') AND
mc_acct_master_view.region IN ('COMM','COOP''SHOW') AND