Adding where clause on a specific date-CodePudding

I need to implement a query where it will allows me to get a category 'Appliances' only if the purchase date is after 2016. What I have now is

Select *
From table
where level = 1 AND Company ='COC' AND Category = 'Appliances' AND PurchaseDate > 2016-01-01

the problem that this will filter all the results to the purchases after 2016. While I want to get all the results but only when the category is 'Appliances' I don't want anything before 2016. How can I do that?

CodePudding user response：

Just use and and or as you might use any boolean algebra. Do not try to over-complicate the logic by combining case expressions as this will prevent the query optimiser from efficiently scanning a date range.

Select *
From table
where level = 1 
    and Company ='COC' 
    and (
        (Category = 'Appliances' AND PurchaseDate > '2016-01-01')
        or
        category <> 'Appliances'
    )

CodePudding user response：

Try using OR in the WHERE clause

Select *
from table
Where (level = 1 AND Company ='COC' AND Category = 'Appliances' AND PurchaseDate > 2016-01-01) 
OR (level = 1 AND Company ='COC' AND Category <> 'Appliances')