CODEWITHSUNDEEP
sqlgoogle-bigquery
SJK
SJK

Reputation: 29

GBQ SQL: Using IF statement in a WHERE statement to filter data

Looking to filter data when multiple conditions are met. The table I have is below:

ID   STOCK   TIME (datetime field)
1    0       2020-06-12T09:20:00.000
2    0       2020-06-11T09:20:00.000
3    10      2020-06-10T09:20:00.000
4    5       2019-01-12T09:20:00.000
5    0       2009-01-12T09:20:00.000
6    0       2009-06-12T09:20:00.000

I need to keep rows that fulfill either one of the follow criteria:

The result table should look like this:

ID   STOCK   TIME (datetime field)
1    0       2020-06-12T09:20:00.000
2    0       2020-06-11T09:20:00.000
3    10      2020-06-10T09:20:00.000
4    5       2019-01-12T09:20:00.000

For my WHERE statement in my code I can do the first part of the criteria but am getting stuck at the second.

WHERE STOCK > 0 OR STOCK <=0 AND TIME BETWEEN datetime_add(current_datetime(), interval -14 day) and current_datetime()

Not sure if the above code is right. Especially after the OR...

Upvotes: 0

Views: 93

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Your expression is correct, meantime it is recommended to use parentheses in such cases to avoid confusion - like in below example 

WHERE STOCK > 0 OR (STOCK <=0 AND TIME BETWEEN datetime_add(current_datetime(), interval -14 day) and current_datetime())

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Your code looks basically correct. But it can be simplified to:

WHERE STOCK > 0 OR 
      TIME >= DATETIME_ADD(CURRENT_DATETIME(), interval -14 day)

This makes two assumptions:

  • stock is not NULL.
  • time is not in the future.

These both seem reasonable.

Upvotes: 1

Related Questions