CODEWITHSUNDEEP
sql-server
Kit
Kit

Reputation: 81

SQL Server condition case doesnt work as intended

I want my SQL to display the overdue count when the condition is the status name showed closed on the exact due date then the count will be set as 1. For example, on the due date, the status name only became closed.

select 
    category, COUNT(overdue) as overdue2 
from 
    (select 
         Category, due,
         case when DATEDIFF(day, Due, SYSDATETIME()) = 0 then 1   
              else 0 
         end as overdue 
     from 
         FeedbackDetail 
     where 
         StatusName = 'Closed' and  
         FeedbackDatetime >= '2018-01-01') a 
Group by 
    Category

My expected result is to display the count where the statusname is closed on the exact due date time.

Any idea on this?

Upvotes: 0

Views: 74

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12959

You can modify the query like given below for better performance and working. 

DECLARE @currentDateTime DATETIME = GETDATE()
select 
    category, SUM(overdue) as overdue2 
from 
    (select 
         Category, 
         case when DATEDIFF(day, Due, @currentDateTime) = 0 then 1   
              else 0 
         end as overdue 
     from 
         FeedbackDetail 
     where 
         StatusName = 'Closed' and  
         FeedbackDatetime >= '2018-01-01') a 
Group by 
    Category

Upvotes: 0

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

The COUNT aggregate function counts existant (non-null) values, so it will count 0 as well as 1. Since you did not post the whole query and we have no idea what a1 is, the only solution that can be proposed is:

Use SUM instead of COUNT.

Upvotes: 1

Related Questions