September 25, 2013 at 3:41 am
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
September 25, 2013 at 5:50 am
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;-)
September 25, 2013 at 6:05 am
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
September 25, 2013 at 6:38 am
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;-)
September 25, 2013 at 6:57 am
No, it's not part of an index in this scenario
September 25, 2013 at 6:59 am
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;-)
September 25, 2013 at 8:32 am
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/
September 26, 2013 at 3:26 am
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://www.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;-)
September 26, 2013 at 6:06 am
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?
September 26, 2013 at 7:17 am
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://www.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/
September 27, 2013 at 6:03 am
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
September 27, 2013 at 6:30 am
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?
September 27, 2013 at 7:10 am
Yes, that's how I'm reading it and Sean's clause should give that. The OP's definitely won't.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply