Dynamically set the result of a TSQL query using CASE WHEN
- by Name.IsNullOrEmpty
SELECT MyTable.Name,(SELECT CASE WHEN ISNULL(SUM(TotalDays), 0) <= 0 THEN 0 ELSE SUM(TotalDays) END AS Total
FROM Application AS Applications
WHERE (ID = MyTable.id)) - MIN(Assignments) AS Excesses
FROM MyTable
The above TSQL statement is a subquery in a main query. When i run it, if TotalDays is NULL or <=0, then Total is set to 0 (zero).
What i would like to do here is to set the result of the whole query(Excesses) to 0. I want (Excesses) which is the result of Total - Min(Assignments) to be set to 0 if its NULL or <=0.
I want the CASE WHEN to apply to the whole query but am struggling to get it right.
