Solved

T-SQL Simple Not Equal Question

Posted on 2008-10-21
4
360 Views
Last Modified: 2010-05-18
Problem Column: dc.itemclass tinyint (not null)
Problem: I want to display a result set that excludes itemclasses assigned the numbers 1, 9, and 20. I am not able to exclude them with my current code and need some help with the correct syntax.

Ways I have unsuccessfully attempted:
example 1
and di.itemclass not like '1'
and di.itemclass not like '9'
and di.itemclass not like '20'
example 2
and not di.itemclass = (1)
and not di.itemclass = (9)
and not di.itemclass = (20)
example 3
and di.itemclass <> (1)
and di.itemclass <> (9)
and di.itemclass <> (20)



Current Code Below:
select
dc.customernk,
dc.customername,      
di.itemclass,
di.itemclassdesc,
      di.ItemSubClass,
di.itemsubclassdesc,
      di.itemnk,
      di.itemdesc,
      sum(f.quantitysold)as 'Net Qty Sold',
Sum((ExtendedPrice - ExtendedPromotions) + (MarketingAllowances + Deposits +
SalesTax + FreightServiceFees + TelxonFees + Othercharges))as  'Net Sales',
case when argroup in (' ') then ('35145-8') else argroup end
FROM
      DimItem as di
JOIN      FactCustSales as f
ON       di.ItemWK = f.ItemWK
JOIN      DimCustomer as dc
ON      f.CustomerWK = dc.CustomerWK
JOIN      DimCalendar as dcal
ON      f.SalesDateWK = dcal.datewk
WHERE       dc.argroup in ('H04', 'H05')
or customernk = ('351458')
AND      dcal.DateNK >= 'Jan 1 2008'
AND di.itemclass not in (1,9,20)
AND      di.Chargetype = 'ITEM'
and f.quantitysold > (0)
and f.creditinvoiceflag <> 'RB'
GROUP BY

dc.customernk,
dc.customername,
argroup,
      di.ItemClass,
      di.ItemClassDesc,
      di.ItemSubClass,
      di.ItemSubClassDesc,
      di.Itemnk,
      di.ItemDesc
ORDER BY
dc.customernk,
dc.customername,      
argroup,
      di.ItemClass asc,
      di.ItemSubClass asc,
      sum(f.quantitysold) desc
0
Comment
Question by:SasDev
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
ID: 22769521
Your WHERE clause is definitely a problem.  Let's examine the logic.  

The basic problem, I believe, is that you have effectively two WHERE clauses - one before the OR and one after.  To solve this, you will need to include the OR'd items within a set of parentheses, like this:

WHERE
    (
        dc.argroup in ('H04', 'H05')
        or customernk = ('351458')
    )
    AND      dcal.DateNK >= 'Jan 1 2008'
    AND di.itemclass not in (1,9,20)
    AND      di.Chargetype = 'ITEM'
    and f.quantitysold > (0)
    and f.creditinvoiceflag <> 'RB'
0
 
LVL 1

Author Closing Comment

by:SasDev
ID: 31508417
How right you are! Thank you very much for helping me examine my logic.
Take Care,

Traci V.
0
 
LVL 3

Expert Comment

by:ScottyNewSuits
ID: 22769591
and di.itemclass NOT IN (1,9,20) should work...

you can try
and (di.itemclass <> 1 AND di.itemclass <> 9 AND di.itemclass <> 20).

Please note that OR syntax ignore indexes.
0
 
LVL 1

Author Comment

by:SasDev
ID: 22770518
Thank you ScottyNewSuits. I already awarded the points before your suggestion came in. Thanks anyhow!

Traci
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ms sql + top 1 for each customer 3 65
Help with simplifying SQL 6 54
create insert script based on records in a table 4 28
SSRS 2013 - Finding Datasets/StoredProcedures 4 37
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question