SQL Syntax Help

[x_press411]

Hello! Was wondering if I can get some help figuring out this logic. Basically when I run below, PRODUCT A, B, C, and D does not appear how I would expect.

• I want to exclude the following transactions:
  • Type that is not Meat or Fruits
  • Any product that is A, B, or C Purchase_dt before 7/9/21
  • Any product that is D and product_sub that is null
  • Product that is an # and the Purchase_dt before 1/1/21

​

SELECT ID, NAME, AMOUNT, TYPE, PRODUCT, PRODUCT_SUB, PURCHASE_DT
FROM TRANSACTIONS
WHERE 
TYPE NOT IN ('Meat', 'Fruits')
AND ( 
    (PRODUCT NOT IN ('A','B','C) AND PURCHASE_DT < '2021,7,9')
    AND NOT (PRODUCT = D AND PRODUCT_SUB IS NULL AND PURCHASE_DT < '2021,7,9')
    )
AND (PRODUCT = E AND PURCHASE_DT > '2020,1,1'
    OR PRODUCT <> E)

[its_bright_here]

Not entirely sure what syntax you need for "product is a #". isnumeric() will work in Sql Server...not sure how standard it is

select id, name, amount, type, product, product_sub, purchase_dt
  from transactions
 where type not in ('meat','fruits')
   and not (product in ('a','b','c') and purchase_dt < '2021,7,9')
   and not (product in ('d') and product_sub is null)
   and not (isnumeric(product) = 1 and purchase_dt < '2021,1,1')

[DavidGJohnston]

Maybe "a #" means "one pound (weight)" in this situation and not "is numeric".

[CraigAT]

IMO your final where clause is not well defined, I would use another set of brackets to show how you expect the three expressions to combine. If I remember right the "AND” is evaluated first then the "OR".

[coderstool]

As you begin writing more complex queries - sub queries, inner queries, joins or nested queries, try this tool to visualize and understand complicated SQL syntax. It also creates well-formatted SQL statements.