SQL CASE WHEN columnvalues = totalcolumnvalues

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

CodePudding user response：

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 ...

CodePudding user response：

use ABS:

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