Simplifying CASE WHEN SQL statement
- by kateroh
Im trying to improve the following CASE statement to calculate the difference only once. I do it to avoid negative numbers:
SELECT (CASE WHEN ((SELECT 100 - (SELECT COUNT(CustomerId) FROM Customers)) > 0)
THEN (SELECT 100 - (SELECT COUNT(CustomerId) FROM Customers))
ELSE (0)
END)
This not only looks stupid, but also is not thread-safe. I tried the following, but I get an error message "Invalid column name 'diff'."
SELECT (CASE WHEN ((SELECT 100 - (SELECT COUNT(CustomerId) FROM Customers) as diff) > 0)
THEN (diff)
ELSE (0)
END)
How can this be simplified? Is there an in-built SQL function that already does this job?
EDIT: Sorry, forgot to mention that the select statement is inside of a view declaration, so I cant declare variables.