T-SQL Query Suggestion-CodePudding

CREATE TABLE #Emp
       (ID int,
       Name varchar(100))

INSERT INTO #Emp VALUES (1,'AAA'),(2,'BBB'),(3,'CCC')

CREATE TABLE #Task
       (EmpID int,
        TaskName varchar(100),
        [Hours] int)


INSERT INTO #Task VALUES (1,'Task-1',2),(1,'Task-2',4),(1,'Task-5',3)
INSERT INTO #Task VALUES (2,'Task-3',2),(2,'Task-4',4),(2,'Task-5',3)
INSERT INTO #Task VALUES (3,'Task-1',2),(3,'Task-1',4),(3,'Task-1',6),(3,'Task-2',3),(3,'Task-6',1)

#Emp
ID  Name
1   AAA
2   BBB
3   CCC

#Task:
EmpID   TaskName    Hours
1       Task-1       2
1       Task-2       4
1       Task-5       3
2       Task-3       2
2       Task-4       4
2       Task-5       8
3       Task-1       2
3       Task-1       4
3       Task-1       6
3       Task-2       3
3       Task-6       1

For each employee I need to get the sum of hours for (Task-1 and Task-2) and for Task-5 Something like below

Name   PrepHours(Task-1   Task-2)   ReviewHours(Task-5)
AAA     6                             3
BBB     0                             8
CCC     15                            0

I tried the below Query but it fails with error Column '#Task.TaskName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Select Name,PrepHours, ReviewHours FROM #Emp as E
Join (Select empid, Case WHEN Taskname in ('Task-1','Task-2') THEN Sum(Hours) ELSE 0 END AS 'PrepHours',
Case WHEN Taskname in ('Task-5') THEN Sum(Hours) ELSE 0 END AS 'ReviewHours'FROM #Task
WHERE Taskname in ('Task-1','Task-2','Task-5')
GROUP BY empid) as t on E.id = t.empid
Order by Name

So If add the Taskname in the Group by it provides multiple rows for each. I need one row for each employee. Need help Please.

Name    PrepHours   ReviewHours
AAA      2           0
AAA      4           0
AAA      0           3
BBB      0           8
CCC     12           0
CCC      3           0

CodePudding user response：

You can create your totals using a conditional case espression in a cross apply

select e.name, t.*
from #emp e
cross apply (
    select 
        Sum(case when taskname in ('task-1','task-2') then hours else 0 end) PrepHours,
        Sum(case when taskname ='Task-5' then hours else 0 end) ReviewHours
    from #task t
    where t.EmpId=e.Id
)t

CodePudding user response：

You can make the original query work by moving eht Sum outside the case statement:

SELECT [Name],
       PrepHours,
       ReviewHours
FROM   #Emp AS E
       JOIN (SELECT empid,
                   Sum( CASE
                      WHEN Taskname IN ( 'Task-1', 'Task-2' ) THEN [Hours]
                      ELSE 0
                    END) AS 'PrepHours',
                    sum(CASE
                      WHEN Taskname IN ( 'Task-5' ) THEN [Hours]
                      ELSE 0
                    END) AS 'ReviewHours'
             FROM   #Task
             WHERE  Taskname IN ( 'Task-1', 'Task-2', 'Task-5' )
             GROUP  BY empid) AS t
         ON E.id = t.empid
ORDER  BY Name