CODEWITHSUNDEEP
sql-server
Umar.H
Umar.H

Reputation: 23099

Nesting a Case statement inside a sum - SQL Server

Having trouble with this :

I have the following query which gives me exactly the view I need.

SELECT t.Week,
       SUM(t.Hours) AS AH,
       SUM(c.Input) AS input,
       SUM(c.[recruitedPos]) AS recruitedPos,
       (SUM(t.Hours) - SUM(c.Input)) AS Possible
FROM tempPOC AS t
     LEFT JOIN (SELECT Week,
                       SUM(input) AS Input,
                       SUM([Normal Weekly Hours]) AS recruitedPos,
                       Shop
                FROM colData
                GROUP BY week,
                         Shop) AS c ON t.Week = c.Week
                                   AND c.Shop = t.Store
GROUP BY t.Week,
         t.Store
ORDER BY t.Week;

What I'm having trouble with is writing a case statement to set any values from possible alias to 0 if they are lower than 0.

I thought this would be as simple as 

(CASE(SUM(t.Hours - c.Hours)) < 0 then 0 else Possible end as Possible)

but this is giving me error

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'as'.

Input : 

Week, AH, Input, RecruitedPos, Possible
1,    15, 25,    13,            -10
1,    30, 15,    15,            15

expected output :

Week, AH, Input, RecruitedPos, Possible
1,    15, 25,    13,            0  
1,    30, 15,    15,            15

Upvotes: 0

Views: 60

Answers (1)

FizzBuzz
FizzBuzz

Reputation: 693

CASE
    WHEN SUM(t.Hours) - SUM(c.Input) < 0 THEN 0
    ELSE SUM(t.Hours) - SUM(c.Input)
END AS Possible

Upvotes: 2

Related Questions