Dynamically set the result of a TSQL query using CASE WHEN

Question

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.

Answer 1

SELECT  MyTable.Name, CASE WHEN ISNULL(SUM(TotalDays), 0) <= 0 THEN 0 ELSE SUM(TotalDays) END AS Total
FROM    Application AS Applications
JOIN    MyTable
ON      Applications.id = mytable.id
GROUP BY
        MyTable.id, MyTable.name
HAVING  CASE WHEN ISNULL(SUM(TotalDays), 0) <= 0 THEN 0 ELSE SUM(TotalDays) END > 0

Answer 2

SELECT
  MyTable.Name,
  CASE WHEN
    0 < (SELECT SUM(TotalDays) FROM Application WHERE ID = MyTable.id) - MIN(Assignments)
  THEN
    (SELECT SUM(TotalDays) FROM Application WHERE ID = MyTable.id) - MIN(Assignments)
  ELSE
    0
  END AS [Excesses]
FROM
  MyTable

Note: MS SQL Server won't exexute the two correlated-sub-queries independantly, it will infact recognise that they are the same and re-use the results.

Alternative:

SELECT
  MyTable.Name,
  CASE WHEN
    0 < SUM([application].TotalDays) - MIN([MyTable].Assignments)
  THEN
    SUM([application].TotalDays) - MIN([MyTable].Assignments)
  ELSE
    0 -- If either aggregate is NULL, 0 will still be returned
  END AS [Excesses]
FROM
  MyTable
LEFT JOIN
  Application
    ON [application].ID = [MyTable].id

Answer 3

Shot in the dark...

SELECT
    mt.Name
    , CASE 
        WHEN ISNULL(MIN(Assignments) - a.Total), 0) <= 0 THEN 0 
        ELSE MIN(Assignments) - a.Total
      END AS Excesses
FROM    
    MyTable mt
INNER JOIN
    (
    SELECT
        ID 
        , CASE 
            WHEN ISNULL(SUM(TotalDays), 0) <= 0 THEN 0 
            ELSE SUM(TotalDays) 
          END AS Total
    FROM    
        Application
    GROUP BY
        ID
    ) a
ON  a.ID = mt.ID
GROUP BY
    mt.Name
    , a.Total