Problem Adding Two Tables and Joining Two Others

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Problem Adding Two Tables and Joining Two Others

Author

Topic

pdmarty
Starting Member

3 Posts

Posted - 2015-03-19 : 13:33:15

I have two tables that I need to add up three columns each and then add those together to get a Totals column. I also need to exclude records from one of those tables via a third tables data and then join a fourth table to get the tech's names. I have gotten this far but need some perspective.

I need to exclude Records from SCReportLabors via a column VoidID in table SCReports. I then need to join SHAgents table to get the tech's name. The column name for the tech's ID in SCReportsLabors & SCQReportLabors is TechnicianID and in SHAgents it's AgentID. The tech's name is in column PrefFullName in SHAgents.

SELECT a.TotalHours, c.PrefFullName AS TechName
FROM (SELECT SUM(LaborHours)+SUM(OvertimeHours)+SUM(TravelHours) AS TotalHours, TechnicianID
		FROM SCReportLabors
		GROUP BY TechnicianID) a
JOIN (SELECT SUM(LaborHours)+SUM(OvertimeHours)+SUM(TravelHours) AS TotalHours, TechnicianID
		FROM SCQReportLabors
		GROUP BY TechnicianID) b
ON a.TechnicianID = b.TechnicianID
LEFT OUTER JOIN ShAgents c ON a.TechnicianID = c.AgentID
ORDER BY c.PrefFullName

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-19 : 14:25:40

Why are you joining to the same derived table twice? Regarding the exclusion, add a join to that table and then a where clause with the filter.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

pdmarty
Starting Member

3 Posts

Posted - 2015-03-19 : 15:25:10

Hi Tara,

It looks like the same table but one starts with SCR and the other SCQR. Don't ask me, I didn't create the database. The problem I am have is binding. Actually, there will be multiple records in SCReportLabors to one record in SCReports. That just dawned on me. I have to believe there is another way to write this to get it to work. I've just been staring at it too long.


SELECT a.TotalHours, c.PrefFullName AS TechName
FROM (SELECT SUM(LaborHours)+SUM(OvertimeHours)+SUM(TravelHours) AS TotalHours, TechnicianID, ReportID
		FROM SCReportLabors
			LEFT OUTER JOIN SCReports d ON a.ReportID = d.ReportID
		WHERE d.VoidID IS NULL
		GROUP BY TechnicianID) a
JOIN (SELECT SUM(LaborHours)+SUM(OvertimeHours)+SUM(TravelHours) AS TotalHours, TechnicianID
		FROM SCQReportLabors
		GROUP BY TechnicianID) b
ON a.TechnicianID = b.TechnicianID
LEFT OUTER JOIN ShAgents c ON a.TechnicianID = c.AgentID
ORDER BY c.PrefFullName

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-19 : 15:29:17

I think you're going to need to post sample data and expected result set using this format for us to help: http://www.sqlservercentral.com/articles/Best+Practices/61537/

It can be a lot of work to get it all together to post like that, but it'll mean we can figure out a solution and don't have to go back and forth with questions. It allows us to test on our own machines.

Sample data is just that: SAMPLE. Does not have to be real, but it does have to reflect the issue.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

pdmarty
Starting Member

3 Posts

Posted - 2015-03-19 : 17:53:40

Hi Tara,

I've scripted the files and included a tab delineated file for each table to import. The link below has all the files.

What's supposed to happen is I need to add all the LaborHours, OvertimeHours and TravelHours from tables SCQReportLabors and SCReportLabors and then add those together to get one TotalHours column grouped by TechnicianID. SCReports holds the main report. You can have multiple records from SCReportLabors to one SCReports record. I need to eliminate any records in SCReportLabors were the VoidID column in SCReports is not null. I then need to get the PrefFullName from SHAgent so when the total hours by tech is displayed it shows the tech's full name. Basically I'm totaling all the techs hours so I can create a report in crystal for our accountant. I also need to be able to select a date range from DispatchDate column. I haven't gotten that far yet.

I'll be out of the office until Monday. I appreciate your help. I'm going to keep working on it when I get a chance this weekend.

[url]http://www.radiosnlights.com/guest/SQLFiles.rar[/url]

Marty

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources