Stored Procedures NOT IN Clause

[brxmas]

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

 

[vongrunt]

Got crystal ball handy?

We cannot determine why data still is incorrect if we don't know what to expect (aka: correct results) for comparison purposes.

And query seems incomplete... feel free to post the rest.



------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

What's the problem?
Also make sure you account for NULLS if one of the values is a NULL in the IN part nothing will return, better to use exists in that case (every case)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[brxmas]

Hope this gives you a little more insight.

For example I do not want to see any records that have an obj_code of 19, 29, 900 and prod_codes of ELDY, INEM. There should only be data for obj_codes 100, 200, 300 and prod_codes of MBDY, MBEX, MBLE. The example below shows what data is coming in now which is incorrect:

23177 SII CT 2005 MBDY 6.0000 .0000 900
23153 SII CT 2005 MBDY 2.0000 .0000 900
23479 AIM CT 2005 MBDY 8.0000 .0000 900
23532 SII CT 2005 MBDY 6.0000 .0000 900
23569 SII CT 2005 MBEX 8.0000 .0000 900
23769 SII RE 2005 MBLE 8.0000 .0000 900

24947 SII CT 2004 ELDY 2.0000 .0000 100
25306 SII CT 2004 ELDY 1.5000 .0000 100
25563 SII CT 2004 ELDY 2.0000 .0000 100
17635 SII CT 2004 ELDY 2.0000 .0000 100
-----------------------------------------------------
The query should only show data below:

2623 AIM CL 2005 MBDY 13.3000 .0000 200
2623 AIM CL 2005 MBDY 13.3000 .0000 100
2623 AIM CL 2005 MBLE 10.2500 .0000 300

This is the complete Stored Procedure

(
@start_date_CY varchar(10)
,@end_date_CY varchar(10)
,@start_date_LY varchar(10)
,@end_date_LY varchar(10)
--,@year int
)

AS

SET NOCOUNT ON

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','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(mc_ad_insertion.insertion_date between @start_date_CY and @end_date_CY)OR
(mc_ad_insertion.insertion_date between @start_date_LY AND @end_date_LY)

GO

Thanks in advance for your help.

 

[SQLDenis]

Well this 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')
should be this
WHERE mc_ad_cost_view.obj_code NOT IN ('19','29','900')

or
WHERE mc_ad_cost_view.obj_code IN (select obj_code from mc_ad_cost_view where obj_code NOT IN ('19','29','900')

not in + (not in) = in






Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[brxmas]

Denis,

I tried both your 'Where' statements and I still get obj_codes 19,29 and 900 and prod_codes 'ELDY'

mc_ad_cost_view.obj_code IN (select obj_code from mc_ad_cost_view where obj_code NOT IN ('19','29','900')) and
mc_ad_insertion.prod_code IN (SELECT prod_code FROM mc_ad_insertion WHERE prod_code NOT IN('ELDY','INEM'))