I need an exclusion query again. My totals are wrong.

Question

Post reply

I need an exclusion query again. My totals are wrong.

frdrckmitchell7

Ten Centuries

Points: 1351
More actions
September 25, 2013 at 3:41 am

#283135

Hi

Can someone put the following perhaps in a query that makes sense. My totals are wrong so I'm thinking that I need a better built query.

'From Item Range CL00000 to 999999. Then exclude 001920, 001921, CL01926, CN00229 and CN00230'

Kind regards

Fred

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

unforturnately ... you probably wont get any response . because people here can help you when you help them/us to help you

MEANS

Post table defintion with sample data along with the query you are using . plus Expected output too . 😛

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 2

Thank you for your response:

I've built the (part of) query like this:

(IM.ItemCode BETWEEN '999999' AND 'CL00000'

OR IM.ItemCode NOT LIKE '001920'

OR IM.ItemCode NOT LIKE '001921'

OR IM.ItemCode NOT LIKE 'CL01926'

OR IM.ItemCode NOT LIKE 'CN00229'

OR IM.ItemCode NOT LIKE 'CN00230')

The total I get is : 3,667,443.85 when I was expecting 3,871,140, short by 203,696.148

This seems to exclude more rows than I expected. I cannot, unfortunately, include the whole query, as it is too long, but the part above makes it wrong. Would you have done it differently, given the info that is included above?

Again I include the user request: From Item Range CL00000 to 999999. Then exclude 001920, 001921, CL01926, CN00229 and CN00230

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Bhuvnesh

SSC Guru

Points: 59351

September 25, 2013 at 6:38 am

#1653102

Also check If itemcode must be part of any index , to get the result faster.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

frdrckmitchell7 Ten Centuries Points: 1351 More actions · Answer 4

frdrckmitchell7

Ten Centuries

Points: 1351

September 25, 2013 at 6:57 am

#1653120

No, it's not part of an index in this scenario

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Bhuvnesh

SSC Guru

Points: 59351

September 25, 2013 at 6:59 am

#1653125

frdrckmitchell7 (9/25/2013)

No, it's not part of an index in this scenario

Then study the related exec plan and see if it ask for improvement(index addition or updation)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Sean Lange SSC Guru Points: 286562 More actions · Answer 6

frdrckmitchell7 (9/25/2013)

Thank you for your response:

I've built the (part of) query like this:

(IM.ItemCode BETWEEN '999999' AND 'CL00000'

OR IM.ItemCode NOT LIKE '001920'

OR IM.ItemCode NOT LIKE '001921'

OR IM.ItemCode NOT LIKE 'CL01926'

OR IM.ItemCode NOT LIKE 'CN00229'

OR IM.ItemCode NOT LIKE 'CN00230')

The total I get is : 3,667,443.85 when I was expecting 3,871,140, short by 203,696.148

This seems to exclude more rows than I expected. I cannot, unfortunately, include the whole query, as it is too long, but the part above makes it wrong. Would you have done it differently, given the info that is included above?

Again I include the user request: From Item Range CL00000 to 999999. Then exclude 001920, 001921, CL01926, CN00229 and CN00230

You don't need to use LIKE when you are looking for a literal. This should produce the same results at above.



IM.ItemCode BETWEEN '999999' AND 'CL00000'

AND IM.ItemCode NOT IN ('001920', '001921', 'CL01926', 'CN00229', 'CN00230')

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

Sean Lange (9/25/2013)

IM.ItemCode BETWEEN '999999' AND 'CL00000'

AND IM.ItemCode NOT IN ('001920', '001921', 'CL01926', 'CN00229', 'CN00230')

[/code]

Sean,

Talking about the selection of "NOT IN" over "Between" i have experienced that Scan count (from set statistics io on ) comes quite high though exec plans behave same any idea about it.

see my post http://qa.sqlservercentral.com/Forums/Topic1496845-391-1.aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Tom Bakerman Hall of Fame Points: 3367 More actions · Answer 8

frdrckmitchell7 (9/25/2013)

Thank you for your response:

I've built the (part of) query like this:

(IM.ItemCode BETWEEN '999999' AND 'CL00000'

OR IM.ItemCode NOT LIKE '001920'

OR IM.ItemCode NOT LIKE '001921'

OR IM.ItemCode NOT LIKE 'CL01926'

OR IM.ItemCode NOT LIKE 'CN00229'

OR IM.ItemCode NOT LIKE 'CN00230')

The total I get is : 3,667,443.85 when I was expecting 3,871,140, short by 203,696.148

This seems to exclude more rows than I expected. I cannot, unfortunately, include the whole query, as it is too long, but the part above makes it wrong. Would you have done it differently, given the info that is included above?

Again I include the user request: From Item Range CL00000 to 999999. Then exclude 001920, 001921, CL01926, CN00229 and CN00230

It looks to me that none of the specific items are excluded (or they are already excluded) because they are all outside the range of 999999 to CL00000, so you don't need the LIKE or NOT IN clauses (001920 and 001921 are < 999999 and the rest are > CL00000). Did you expect this, and could it be part of the reason the result is NOT what you expected?

Sean Lange SSC Guru Points: 286562 More actions · Answer 9

Bhuvnesh (9/26/2013)

Sean Lange (9/25/2013)

IM.ItemCode BETWEEN '999999' AND 'CL00000'

AND IM.ItemCode NOT IN ('001920', '001921', 'CL01926', 'CN00229', 'CN00230')

[/code]

Sean,

Talking about the selection of "NOT IN" over "Between" i have experienced that Scan count (from set statistics io on ) comes quite high though exec plans behave same any idea about it.

see my post http://qa.sqlservercentral.com/Forums/Topic1496845-391-1.aspx

This is really nothing like what you are discussing in that other thread. I would listen to Gail over me on pretty much anything involving sql server. Her knowledge exceeds mine by leaps and bounds. If she says to ignore because it is meaningless, then follow her advice. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 10

Sean Lange (9/25/2013)

frdrckmitchell7 (9/25/2013)

Thank you for your response:

I've built the (part of) query like this:

(IM.ItemCode BETWEEN '999999' AND 'CL00000'

OR IM.ItemCode NOT LIKE '001920'

OR IM.ItemCode NOT LIKE '001921'

OR IM.ItemCode NOT LIKE 'CL01926'

OR IM.ItemCode NOT LIKE 'CN00229'

OR IM.ItemCode NOT LIKE 'CN00230')

The total I get is : 3,667,443.85 when I was expecting 3,871,140, short by 203,696.148

This seems to exclude more rows than I expected. I cannot, unfortunately, include the whole query, as it is too long, but the part above makes it wrong. Would you have done it differently, given the info that is included above?

Again I include the user request: From Item Range CL00000 to 999999. Then exclude 001920, 001921, CL01926, CN00229 and CN00230

You don't need to use LIKE when you are looking for a literal. This should produce the same results at above.

IM.ItemCode BETWEEN '999999' AND 'CL00000' AND IM.ItemCode NOT IN ('001920', '001921', 'CL01926', 'CN00229', 'CN00230')

Well it should produce the results he's asking for. The clause he gives will include everything non null thanks to all those ORs

OR IM.ItemCode NOT LIKE '001920'

will include everything except 001920

OR IM.ItemCode NOT LIKE '001921'

will include everything except 001921

ORing them together will include everything

Tom Bakerman Hall of Fame Points: 3367 More actions · Answer 11

crmitchell (9/27/2013)

Sean Lange (9/25/2013)

frdrckmitchell7 (9/25/2013)

Thank you for your response:

I've built the (part of) query like this:

(IM.ItemCode BETWEEN '999999' AND 'CL00000'

OR IM.ItemCode NOT LIKE '001920'

OR IM.ItemCode NOT LIKE '001921'

OR IM.ItemCode NOT LIKE 'CL01926'

OR IM.ItemCode NOT LIKE 'CN00229'

OR IM.ItemCode NOT LIKE 'CN00230')

The total I get is : 3,667,443.85 when I was expecting 3,871,140, short by 203,696.148

This seems to exclude more rows than I expected. I cannot, unfortunately, include the whole query, as it is too long, but the part above makes it wrong. Would you have done it differently, given the info that is included above?

Again I include the user request: From Item Range CL00000 to 999999. Then exclude 001920, 001921, CL01926, CN00229 and CN00230

You don't need to use LIKE when you are looking for a literal. This should produce the same results at above.

IM.ItemCode BETWEEN '999999' AND 'CL00000' AND IM.ItemCode NOT IN ('001920', '001921', 'CL01926', 'CN00229', 'CN00230')

Well it should produce the results he's asking for. The clause he gives will include everything non null thanks to all those ORs

OR IM.ItemCode NOT LIKE '001920'

will include everything except 001920

OR IM.ItemCode NOT LIKE '001921'

will include everything except 001921

ORing them together will include everything

To me, this doesn't match with the user request:

Again I include the user request: From Item Range CL00000 to 999999. Then exclude 001920, 001921, CL01926, CN00229 and CN00230

I read that as Items BETWEEN 999999 AND CL00000 AND NOT = 001920 AND NOT = 001921 AND NOT = ...

Am I misinterpreting, or is the OP? If the query should be ORing together those special cases, then the BETWEEN range is not needed because a row such as CL000001 will be included in the results because it is not like any of the special cases. If the query should be ANDing the special cases, then they do not need to be in the query because they don't add any conditions that the BETWEEN clause already filter.

Or is it just too early in the morning for me?

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 12

crmitchell

SSCarpal Tunnel

Points: 4557

September 27, 2013 at 7:10 am

#1654008

Yes, that's how I'm reading it and Sean's clause should give that. The OP's definitely won't.