Solved

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

Posted on 2013-11-19
12
279 Views
Last Modified: 2014-03-23
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')

Open in new window


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

Open in new window


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!
0
Comment
Question by:Bianca
  • 7
  • 4
12 Comments
 
LVL 4

Expert Comment

by:rshq
ID: 39660437
Hi
  I think it is better  use "Group By" on Employee and SUM(hours).

SELECT     dbo.Training_Hours_View.FullName, SUM(A.Hours) AS DoorOrder_Hours, SUM(B.Hours) AS JobPrep_Hours, SUM(C.Hours) AS JobEng_Hours, SUM(D.Hours) AS JobCheck_Hours, SUM(E.Hours) AS JobReview_Hours, 
                      SUM(F.Hours) AS Other_Hours
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')
Group By dbo.Training_Hours_View.FullName

Open in new window

0
 

Author Comment

by:Bianca
ID: 39660668
@rshq that works great except I need to include the date in my select so my associated grid can pull data based on a date or date range.  When I add 'Date' it adds to the Group By, therefore changing the results.  Your thoughts?
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39660754
See if this works...

I'm concerned that you might get some duplicated records from the root view reference but it's hard to test without real data.

SELECT THV.ID,
	THV.Emp_SubDept,
	THV.FullName,
	THV.Date,		--assuming dates do not have a time component
	SUM(THV_DoorOrders.Hours) AS DoorOrder_Hours,
	SUM(THV_JobPrep.Hours) AS JobPrep_Hours,
	SUM(THV_JobEng.Hours) AS JobEng_Hours,
	SUM(THV_JobCheck.Hours) AS JobCheck_Hours,
	SUM(THV_JobReview.Hours) AS JobReview_Hours, 
	SUM(THV_Other.Hours) AS Other_Hours,
	THV.TaskName,
	THV.Task_SubDept
FROM dbo.Training_Hours_View AS THV
LEFT OUTER JOIN dbo.Training_Hours_View AS THV_DoorOrders
   ON THV.FullName = THV_DoorOrders.FullName
   AND THV.Date = THV_DoorOrders.Date
   AND THV_DoorOrders.TaskName = 'Door Orders'
LEFT OUTER JOIN dbo.Training_Hours_View AS THV_JobPrep
   THV.FullName = THV_JobPrep.FullName
   AND THV.Date = THV_JobPrep.Date
   AND THV_JobPrep.TaskName = 'Job Prep'
LEFT OUTER JOIN dbo.Training_Hours_View AS THV_JobEng
   THV.FullName = THV_JobEng.FullName
   AND THV.Date = THV_JobEng.Date
   AND THV_JobEng.TaskName = 'Job Eng'
LEFT OUTER JOIN dbo.Training_Hours_View AS THV_JobCheck
   THV.FullName = THV_JobCheck.FullName
   AND THV.Date = THV_JobCheck.Date
   AND THV_JobCheck.TaskName = 'Job Check'
LEFT OUTER JOIN dbo.Training_Hours_View AS THV_JobReview
   THV.FullName = THV_JobReview.FullName
   AND THV.Date = THV_JobReview.Date
   AND THV_JobReview.TaskName = 'Job Review'
LEFT OUTER JOIN dbo.Training_Hours_View AS THV_Other
   THV.FullName = THV_Other.FullName
   AND THV.Date = THV_Other.Date
   AND THV_Other.TaskName = 'Other'
   AND THV_Other.Task_SubDept = 'Engineering'
WHERE THV.Task_SubDept LIKE 'Engineering'
GROUP BY THV.ID,
	THV.Emp_SubDept,
	THV.FullName,
	THV.Date,
	THV.TaskName,
	THV.Task_SubDept

Open in new window

0
 

Author Comment

by:Bianca
ID: 39660766
That is giving me a syntax error on THV.. on a side note about your last comment, the view I am using MAY be the reason for my duplicate records. I will attempt to point your previous view (which worked except it did not have date) to the base table...
0
 

Author Comment

by:Bianca
ID: 39660879
UPDATE:
I added 'AND (dbo.Training_Hours_View.Date = '9/18/13')' to the end of the 1st query you sent and it gives me the correct results ONLY if there is one record instance. For those employees that have more than 1 task, it is duplicating the value. See below results.

Andrew only did 1 task for this day and it was for 8.5 hours. That shows correct. For those that did more than 1 task, Francis for example, the 1st value should be 0.5 and 2nd value is 8 (totaling to 8.5 hours for the day) but because there are 2 records, it is duplicating the total hours.

Andrew Myers	NULL	NULL	8.5	NULL	NULL	NULL
Francis Pascua	NULL	1	16	NULL	NULL	NULL
Mike Simpson	NULL	NULL	14	NULL	NULL	3
Paola Casas	10	NULL	NULL	NULL	NULL	7
Steve Burnett	NULL	NULL	NULL	NULL	14	5

Open in new window

0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39660904
The problem is the root set reference to Traning_Hours_View.  Your base set needs contain a single record per task/date.

FROM (SELECT DISTINCT ID, Emp_SubDept, FullName, Date, TaskName FROM Training_Hours_View) AS THV

This is assuming that the ID column is referring to an employee in which case you should be joining on that instead of FullName
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Bianca
ID: 39660932
Yes, the ID column is EmpID.  Here is what I have currently that is giving me duplicate tasks:

SELECT     dbo.Training_Hours.EmpID, SUM(A.Hours) AS DoorOrder_Hours, SUM(B.Hours) AS JobPrep_Hours, SUM(C.Hours) AS JobEng_Hours, SUM(D.Hours) 
                      AS JobCheck_Hours, SUM(E.Hours) AS JobReview_Hours, SUM(F.Hours) AS Other_Hours
FROM         (SELECT     ID, TaskID, Hours, Date, EmpID
                       FROM          dbo.Training_Hours AS Training_Hours_View_2
                       WHERE      (TaskID = 2)) AS B RIGHT OUTER JOIN
                      dbo.Training_Hours ON B.Date = dbo.Training_Hours.Date AND B.EmpID = dbo.Training_Hours.EmpID LEFT OUTER JOIN
                          (SELECT     ID, TaskID, Hours, Date, EmpID
                            FROM          dbo.Training_Hours AS Training_Hours_View_6
                            WHERE      (TaskID = 6)) AS F ON dbo.Training_Hours.Date = F.Date AND dbo.Training_Hours.EmpID = F.EmpID LEFT OUTER JOIN
                          (SELECT     ID, TaskID, Hours, Date, EmpID
                            FROM          dbo.Training_Hours AS Training_Hours_View_5
                            WHERE      (TaskID = 5)) AS E ON dbo.Training_Hours.Date = E.Date AND dbo.Training_Hours.EmpID = E.EmpID LEFT OUTER JOIN
                          (SELECT     ID, TaskID, Hours, Date, EmpID
                            FROM          dbo.Training_Hours AS Training_Hours_View_4
                            WHERE      (TaskID = 4)) AS D ON dbo.Training_Hours.Date = D.Date AND dbo.Training_Hours.EmpID = D.EmpID LEFT OUTER JOIN
                          (SELECT     ID, TaskID, Hours, Date, EmpID
                            FROM          dbo.Training_Hours AS Training_Hours_View_3
                            WHERE      (TaskID = 3)) AS C ON dbo.Training_Hours.Date = C.Date AND dbo.Training_Hours.EmpID = C.EmpID LEFT OUTER JOIN
                          (SELECT     ID, TaskID, Hours, Date, EmpID
                            FROM          dbo.Training_Hours AS Training_Hours_View_1
                            WHERE      (TaskID = 1)) AS A ON dbo.Training_Hours.EmpID = A.EmpID AND dbo.Training_Hours.Date = A.Date
WHERE     (dbo.Training_Hours.Date = '9/18/13')
GROUP BY dbo.Training_Hours.EmpID

Open in new window

0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39660940
Try this...

SELECT THV.ID, THV.Emp_SubDept, THV.FullName, THV.[Date],
      THV.TaskName, THV.Task_SubDept,
      SUM(CASE WHEN TaskName = 'Door Orders' THEN THV.Hours ELSE 0 END) AS DoorOrder_Hours,
      SUM(CASE WHEN TaskName = 'Job Prep' THEN THV.Hours ELSE 0 END) AS JobPrep_Hours,
      SUM(CASE WHEN TaskName = 'Job Eng' THEN THV.Hours ELSE 0 END) AS JobEng_Hours,
      SUM(CASE WHEN TaskName = 'Job Check' THEN THV.Hours ELSE 0 END) AS JobCheck_Hours,
      SUM(CASE WHEN TaskName = 'Job Review' THEN THV.Hours ELSE 0 END) AS JobReview_Hours,
    SUM(CASE WHEN TaskName = 'Other' THEN THV.Hours ELSE 0 END) AS Other_Hours
FROM dbo.Training_Hours_View AS THV
WHERE THV..Task_SubDept = 'Engineering'
GROUP BY THV.ID, THV.Emp_SubDept, THV.FullName, THV.[Date],
      THV.TaskName, THV.Task_SubDept
0
 

Author Comment

by:Bianca
ID: 39660954
That gives me 610 records and none of them summed values. It showed all records with each 'hour type' split out.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39660962
Try removing SubDept from the selection and GROUP BY Clause.  I'm just shooting in the dark without any insight into your data.
0
 

Accepted Solution

by:
Bianca earned 0 total points
ID: 39661010
This view:
SELECT     ID, Emp_SubDept, FullName, Date, TaskName, Task_SubDept, SUM(CASE WHEN TaskName = 'Door Orders' THEN THV.Hours ELSE 0 END) 
                      AS DoorOrder_Hours, SUM(CASE WHEN TaskName = 'Job Prep' THEN THV.Hours ELSE 0 END) AS JobPrep_Hours, 
                      SUM(CASE WHEN TaskName = 'Job Eng' THEN THV.Hours ELSE 0 END) AS JobEng_Hours, 
                      SUM(CASE WHEN TaskName = 'Job Check' THEN THV.Hours ELSE 0 END) AS JobCheck_Hours, 
                      SUM(CASE WHEN TaskName = 'Job Review' THEN THV.Hours ELSE 0 END) AS JobReview_Hours, 
                      SUM(CASE WHEN TaskName = 'Other' THEN THV.Hours ELSE 0 END) AS Other_Hours
FROM         dbo.Training_Hours_View AS THV
WHERE     (Task_SubDept = 'Engineering')
GROUP BY ID, Emp_SubDept, FullName, Date, TaskName, Task_SubDept

Open in new window


gives me a dataset that I can group by FullName gives me the correct data BUT the view is read only.
0
 

Author Closing Comment

by:Bianca
ID: 39948365
This is the only solution that worked I had come up with it from trial and error. Other solutions were not giving me results and/or were erroring out.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now