Search code examples
sqlgoogle-bigqueryansi-sql

SQL CASE WHEN columnvalues = totalcolumnvalues +- 1

I need some help figuring out how to do a plus/minus 1 range in my query.

I am trying to make something like the below to work

SELECT 

CASE WHEN (columnvalues1 + columnvalues2 + columnvalues3) = totalcolumnvalues +- 1 THEN 1 END AS flag

FROM table1

so if the result is 37, then a 36, 37 or 38 will be within the +- 1 range and therefore flagged

Any help would be appreciated

Solution

  • Assuming the total is a float or decimal you can simply use a BETWEEN clause:

    CASE WHEN columnvalues1 + columnvalues2 + columnvalues3 BETWEEN
    totalcolumnvalues - 1 AND totalcolumnvalues + 1
THEN ...