T-SQL query suggestion

user_14910677 0
Viewed 61
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),
       (2, 'Task-3', 2), (2, 'Task-4', 4), (2, 'Task-5', 3),
       (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 query shown here, 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 I 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
tsql sql-server sql
2 Answers

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
user_153326500

You can make the original query work by moving the 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
user_8620290
Related