Jacob Cook Jacob Cook - 17 days ago 6
SQL Question

SQL / SSRS 2008 - How to combine 2 conditions into a single condition in the WHERE clause?

I have a requirement which appears simple enough but i cannot get this to work. I want to exclude a particular record in my resultset that meets TWO conditions, click the following image to see my current resultset: https://i.stack.imgur.com/z93m3.png . I want to exclude any lines that have both a selling_price of £84.99 AND a vat_value of £40.83, in my case that would mean i want the last line of my resultset image excluded.

I've attempted to use the following as my SQL code:

SELECT created_by, sales_order_number, sales_order_credit_line, qty_credited, sales_order_line, selling_price, vat_value

FROM sales_order_credit

WHERE (selling_price <> 84.99 AND vat_value <> 40.83)


However, as soon as i go to then run the query, the Query Designer reformats my code to read:

SELECT created_by, sales_order_number, sales_order_credit_line, qty_credited, sales_order_line, selling_price, vat_value

FROM sales_order_credit

WHERE (selling_price <> 84.99) AND (vat_value <> 40.83)


Because my code now reads like that, it excludes all lines with a selling price of £84.99 and all lines with a vat_value of £40.83, which is not what i want - i only want to exclude lines that adhere to BOTH of those conditions.

I've ran into this before and never known how this should be done - any help is really appreciated.

Jacob

EDIT - Responses to Comments

VKP - thanks for your suggestion, when i add your code it changes to:

SELECT created_by, sales_order_number, sales_order_credit_line, qty_credited, sales_order_line, selling_price, vat_value
FROM sales_order_credit
WHERE (NOT (selling_price = 84.99))
OR
(NOT (vat_value = 40.83))


Again, it takes the two and puts them into completely seperate conditions seperated by OR - which is not what im looking for

Ubiquitous Developers - there is a difference, if you do the reformatted version of the code it will exclude line 4 AND 2, whereas i only want line 4 excluded as it meets both conditions, whereas line 2 only met 1 of the conditions (selling_price)

Answer

I think the right thing to do in your case is the next:

SELECT     created_by, sales_order_number, sales_order_credit_line,  qty_credited, sales_order_line, selling_price, vat_value
FROM         sales_order_credit
WHERE     NOT (selling_price = 84.99 AND vat_value = 40.83)