Simplifying CASE WHEN SQL statement

Posted by kateroh on Stack Overflow See other posts from Stack Overflow or by kateroh
Published on 2011-01-08T00:18:13Z Indexed on 2011/01/08 0:53 UTC
Read the original article Hit count: 118

Filed under:
|
|

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server