Looking to filter data when multiple conditions are met. The table I have is below: I need to keep rows that fulfill either one of the follow criteria: If STOCK > 0 THEN KEEP ROW If STOCK <= 0 AND TIME is within 2 weeks from today THEN KEEP ROW The result table should look like this: For my WHERE

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:

If STOCK > 0 THEN KEEP ROW
If STOCK <= 0 AND TIME is within 2 weeks from today THEN KEEP ROW

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…

Answer

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.

Advertisement

Answer