User_K47
User_K47

Reputation: 65

How to calculate the status of a task within a week

I want to calculate the status of a task against end date. In my codes below in completed column it's displaying A = 0 instead of A = 1 as my result.

ID EndDate Task Status
A 2023-06-11 Analysing In progess
B 2023-06-11 Developing Not started
C 2023-06-11 Training In progress
A 2023-06-11 Marketing Completed
DECLARE @currentWeek int
SELECT @currentWeek = DATEPART(week, GETDATE())
SELECT  
    Employee as ID, 
    Sum(CASE WHEN Status  = 'Completed'  THEN  1 ELSE 0 END) as Completed,
    (Sum(CASE WHEN Status  = 'In progress'  THEN 1 ELSE 0 END) +    
    Sum(CASE WHEN Status  = 'Not started'  THEN 1 ELSE 0 END)) as Remaining,
    Sum(CASE WHEN EndDate  >  convert(varchar,GETDATE(),103) THEN 1 ELSE 0 END) as OverDue  
FROM Task
WHERE DATEPART(week, [EndDate]) = @currentWeek  and Status !=  'Completed' 
Group By Employee

Expected output when today's date is 2023-06-12

ID Completed Remaining Over due
A 1 1 1
B 0 1 1
C 0 1 1

Upvotes: 1

Views: 29

Answers (0)

Related Questions