CODEWITHSUNDEEP
sqlsql-server
ifthenifthen
ifthenifthen

Reputation: 275

A query where one "and" condition doesn't always apply

I basically want to create an "if" function in a SQL query. Here is the query:

SELECT sum(amount), count(*) 
FROM Facilities 
    WHERE date = @Date 
    and system = 'LNI' 
    and entity is not NULL 
    and amount > 0
    and cust_cd != 'M' 
    and default_status = 'ACC' 
    and product_code NOT IN ('A', 'B', 'C')

    and expiration_date >= @Date

The last condition only applies to some of the rows captured in this query.

What I want to dream up into existence is something like:

   and (product_code IN ('D', 'E')
      and expiration_date >= @Date )
 +
   and ( product_code IN ('F', 'G')
     and expiration_date = expiration_date)

I hope I am explaining my question correctly. Is there a way to make an "and" condition conditional on other factors, or do I have to separate this into two nearly identical queries?

Upvotes: 0

Views: 185

Answers (2)

Caleth
Caleth

Reputation: 63029

An alternative

AND ( CASE 
   WHEN product_code IN ('D', 'E') THEN expiration_date >= @Date 
   WHEN product_code IN ('F', 'G') THEN expiration_date = @Date
   ELSE TRUE 
END )

This avoids having to synthesize the final lack of condition (@HoneyBadger's product_code NOT IN ('D', 'E', 'F', 'G') OR product_code IS NULL)

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15150

AND (
        (product_code IN ('D', 'E') AND expiration_date >= @Date)
     OR (product_code IN ('F', 'G') AND expiration_date = @Date)
     OR (product_code NOT IN ('D', 'E', 'F', 'G') OR product_code IS NULL) -- This line will make sure you get the rest. If all you want is the D/E and F/G conditions, remove this.
    )

This is probably the best way to do it. Of course things get a bit hairier if @Date can be NULL. These sort of 'catch-all' queries are notoriously difficult.

Upvotes: 2

Related Questions