CODEWITHSUNDEEP
sqlsql-serverdatabase
Brad
Brad

Reputation: 1480

SQL case expression to create a range

I am currently using Microsoft SQL Server. I am trying to work with case expressions within my view to create a range.

Here is an example:

Here is what I attempted.

CONVERT(varchar(12)
       ,(CASE WHEN (X.AVG - (2 * X.AVG)) < 0
          THEN 0
          END
         + '-'
         + CASE WHEN (X.AVG + (2 * X.AVG)) > 100
           THEN 100
           END
        )) as Range

Upvotes: 1

Views: 579

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

You can massively simplify this entire case expression to this. It may be what you actually want as it seems to contradict your written explanation but it logically is the same thing you have coded.

case 
    when X.AVG * 3 > 100 then '100'
    when X.AVG > 0 then '0'
    else 'Invalid' --Or maybe you want to put convert(varchar(12), X.AVG) here?
end

Upvotes: 1

iamdave
iamdave

Reputation: 12243

On top of the missing parenthesis you have since edited in, you also need to include an else clause, as your version doesn't return a value if your calculation is above 0 in the first case or below 100 in your second one:

CONVERT(varchar(12)
       ,(CASE WHEN (X.AVG - (2 * X.AVG)) < 0
          THEN 0
          ELSE X.AVG - (2 * X.AVG)
          END
         + '-'
         + CASE WHEN (X.AVG + (2 * X.AVG)) > 100
           THEN 100
           ELSE X.AVG - (2 * X.AVG)
           END
        )
       ) as Range

Upvotes: 1

Related Questions