Reputation: 65
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