If the value in sql is positive, how can I do this operation if it is negative?-CodePudding

I have a view query in SQL. If the values are positive in my query, I don't have a problem right now. But when the values are negative, the wrong operation is done. Here's how I can separate the operations of returning a positive value from a negative value with the case when query.

My query is:

CASE WHEN ((NM.Min <= TT._VALUE) AND (TT._VALUE <= NM.Max)) THEN 1 ELSE 0 END AS Min_max_Status

Here, when the NM.Min and NM.Max values are positive, for example, when NM.Min = 10 NM.Max = 100, there is no problem no matter what value my TT._VALUE takes. But when NM.Min = -10 when NM.Max =-100 and TT._VALUE = -90, it prints 0 value in Min_max_Status column because it does not provide the small-large equality here. How can I fix this problem. Thank you in advance for your help

CodePudding user response：

First of all, we can rewrite your CASE expression using BETWEEN:

CASE WHEN TT._VALUE BETWEEN NM.Min AND NM.Max THEN 1 ELSE 0 END AS Min_max_Status

As for your example of NM.Min = -10, NM.Max = -100, and TT._VALUE = -90, I reckon that you should phrase it as:

NM.Min = -100, NM.Max = -10, and TT._VALUE = -90

Using the above version will result in the expected output.