Solved

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

Posted on 2013-11-19
12
280 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

932 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

8 Experts available now in Live!

Get 1:1 Help Now