troubleshooting Question

SQL View Query Help - need my join to give me SUM of hours for DISTINCT employee names

Avatar of Norm-al
Norm-alFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
12 Comments1 Solution329 ViewsLast Modified:
The results that I am getting show duplicate employee records and duplicating their values..  Here is my view:
SELECT     dbo.Training_Hours_View.ID, dbo.Training_Hours_View.Emp_SubDept, dbo.Training_Hours_View.FullName, dbo.Training_Hours_View.Date, 
                      A.Hours AS DoorOrder_Hours, B.Hours AS JobPrep_Hours, C.Hours AS JobEng_Hours, D.Hours AS JobCheck_Hours, E.Hours AS JobReview_Hours, 
                      F.Hours AS Other_Hours, dbo.Training_Hours_View.TaskName, dbo.Training_Hours_View.Task_SubDept
FROM         dbo.Training_Hours_View LEFT OUTER JOIN
                          (SELECT     ID, TaskName, Hours, Date, FullName
                            FROM          dbo.Training_Hours_View AS Training_Hours_View_1
                            WHERE      (TaskName LIKE 'Door Orders')) AS A ON dbo.Training_Hours_View.FullName = A.FullName AND 
                      dbo.Training_Hours_View.Date = A.Date LEFT OUTER JOIN
                          (SELECT     ID, TaskName, Hours, Date, FullName
                            FROM          dbo.Training_Hours_View AS Training_Hours_View_2
                            WHERE      (TaskName LIKE 'Job Prep')) AS B ON dbo.Training_Hours_View.Date = B.Date AND 
                      dbo.Training_Hours_View.FullName = B.FullName LEFT OUTER JOIN
                          (SELECT     ID, TaskName, Hours, Date, FullName
                            FROM          dbo.Training_Hours_View AS Training_Hours_View_3
                            WHERE      (TaskName LIKE 'Job Eng')) AS C ON dbo.Training_Hours_View.Date = C.Date AND 
                      dbo.Training_Hours_View.FullName = C.FullName LEFT OUTER JOIN
                          (SELECT     ID, TaskName, Hours, Date, FullName
                            FROM          dbo.Training_Hours_View AS Training_Hours_View_4
                            WHERE      (TaskName LIKE 'Job Check')) AS D ON dbo.Training_Hours_View.Date = D.Date AND 
                      dbo.Training_Hours_View.FullName = D.FullName LEFT OUTER JOIN
                          (SELECT     ID, TaskName, Hours, Date, FullName
                            FROM          dbo.Training_Hours_View AS Training_Hours_View_5
                            WHERE      (TaskName LIKE 'Job Review')) AS E ON dbo.Training_Hours_View.Date = E.Date AND 
                      dbo.Training_Hours_View.FullName = E.FullName LEFT OUTER JOIN
                          (SELECT     ID, TaskName, Task_SubDept, Hours, Date, FullName
                            FROM          dbo.Training_Hours_View AS Training_Hours_View_6
                            WHERE      (TaskName LIKE 'Other') AND (Task_SubDept LIKE 'Engineering')) AS F ON dbo.Training_Hours_View.Date = F.Date AND 
                      dbo.Training_Hours_View.FullName = F.FullName
WHERE     (dbo.Training_Hours_View.Task_SubDept LIKE 'Engineering')

and here are my results:
248	01 - Engineering	Andrew Myers	9/18/2013 12:00:00 AM	NULL	NULL	8.5	NULL	NULL	NULL	Job Eng
153	01 - Engineering	Francis Pascua	9/18/2013 12:00:00 AM	NULL	0.5	8	NULL	NULL	NULL	Job Prep
246	01 - Engineering	Francis Pascua	9/18/2013 12:00:00 AM	NULL	0.5	8	NULL	NULL	NULL	Job Eng
247	01 - Engineering	Mike Simpson	9/18/2013 12:00:00 AM	NULL	NULL	7	NULL	NULL	1.5	Job Eng
489	01 - Engineering	Mike Simpson	9/18/2013 12:00:00 AM	NULL	NULL	7	NULL	NULL	1.5	Other
15	03 - Face Frame	Paola Casas	9/18/2013 12:00:00 AM	5	NULL	NULL	NULL	NULL	3.5	Door Orders
488	03 - Face Frame	Paola Casas	9/18/2013 12:00:00 AM	5	NULL	NULL	NULL	NULL	3.5	Other
416	01 - Engineering	Steve Burnett	9/18/2013 12:00:00 AM	NULL	NULL	NULL	NULL	7	2.5	Job Review
487	01 - Engineering	Steve Burnett	9/18/2013 12:00:00 AM	NULL	NULL	NULL	NULL	7	2.5	Other

It should NOT have duplicate names and duplicate values. It should show each employee once and show the SUM of the hours.

Thank you for your assistance!
ASKER CERTIFIED SOLUTION
Norm-al
Network Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros