CODEWITHSUNDEEP
sql-server
Bob
Bob

Reputation: 127

SQL if a Case statement returns a negative number change to zero

I have a SQL statement that works fine for the most. I need to subtract 2 from the total sum but on if that would not result in a negative number. If it does result in a negative number then I need a zero. I can't use a count I have to use Sum. Basically what I am doing is if the task is finished this week count it -2 but that results in negative numbers and while I can have zero tasks due this week I can't have negative 2 due this week. The -2 is necessary because of some other fields that I can't eliminate that get counted in the total.

Question: How can I write this to only subtract 2 if it will not result in a negative number

,SUM(case when
 T.[TaskFinishDate] <= DATEADD(DAY, 8-DATEPART(DW, GETDATE()), 
Convert(date,getdate()))  then 1 
    else 0 
    end) - 2 AS Planned

Upvotes: 1

Views: 3230

Answers (1)

user6691848
user6691848

Reputation:

You can check your case for a < 0 value and return 0 for less than zero:

case when
    SUM(
        case when t.[TaskFinishDate] <= DATEADD(DAY, 8-DATEPART(DW, GETDATE()), Convert(date,getdate()))then 1 else 0 end) - 2 < 0 
        then 0 
        else 
            SUM(case when t.[TaskFinishDate] <= DATEADD(DAY, 8-DATEPART(DW, GETDATE()), Convert(date,getdate())) then 1 else 0 end) - 2
end planned

Upvotes: 1

Related Questions