Select AVG help. - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Select AVG help.

Author

Topic

swilli6926
Starting Member

4 Posts

Posted - 2010-07-13 : 14:21:57

I have the following t-sql that returns several columns and calculations.

the end result I am looking for is to have the last column calculated as the average for each person. Right now I get several records per person with a value for totalTeamMembers......I am trying to get it down to one result per person and the average of all the totalTeamMembers per person.

Any ideas?

SELECT tblEmployee.employeeLastName + ', ' + tblEmployee.employeeFirstName AS empFullName, SUM(DateDiff('h', Format(tblProduction.startDate, 
                      'short date') + ' ' + tblStartTime.[time], Format(tblProduction.endDate, 'short date') + ' ' + tblEndTime.[time])) AS totalHours, tblProducts.sam, 
                      tblProduction.totalProduced, tblTeams.teamName, tblTeams.teams_ID, tblEmployee.baseRate,
                          (SELECT COUNT(tblTeamMembers.employee_ID)  FROM  tblTeamMembers WHERE tblTeamMembers.teams_Id = tblTeams.teams_ID) AS countTeamMembers, (tblProducts.sam * tblProduction.totalProduced) 
                      / ((SUM(DateDiff('h', Format(tblProduction.startDate, 'short date') + ' ' + tblStartTime.[time], Format(tblProduction.endDate, 'short date') 
                      + ' ' + tblEndTime.[time]))) * countTeamMembers) / 60 AS totalTeamMembers
FROM         ((tblTeams INNER JOIN
                      (tblProducts INNER JOIN
                      ((tblProduction INNER JOIN
                      tblTime tblStartTime ON tblProduction.startTime = tblStartTime.time_ID) INNER JOIN
                      tblTime tblEndTime ON tblProduction.endTime = tblEndTime.time_ID) ON tblProducts.product_ID = tblProduction.product_ID) ON 
                      tblTeams.teams_ID = tblProduction.teams_ID) INNER JOIN
                      (tblEmployee RIGHT OUTER JOIN
                      tblTeamMembers ON tblEmployee.employee_ID = tblTeamMembers.employee_ID) ON tblTeams.teams_ID = tblTeamMembers.teams_Id)
GROUP BY tblEmployee.employeeLastName + ', ' + tblEmployee.employeeFirstName, tblProducts.sam, tblProduction.totalProduced, tblTeams.teamName, 
                      tblTeams.teams_ID, tblEmployee.baseRate
ORDER BY tblEmployee.employeeLastName + ', ' + tblEmployee.employeeFirstName

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-13 : 15:15:17

might this work for you, tweak it up because you have not provided sample data it is hard to figure it out. Did you use some kind of sql wizard to create the join? I am getting crossed eyed :)


WITH clean_it_up
AS (
SELECT tblEmployee.employeeLastName + ', ' + tblEmployee.employeeFirstName AS empFullName, 
       SUM(DateDiff('h', Format(tblProduction.startDate, 'short date') + ' ' + tblStartTime.[time], Format(tblProduction.endDate, 'short date') + ' ' + tblEndTime.[time])) AS totalHours, 
       tblProducts.sam, 
       tblProduction.totalProduced, 
       tblTeams.teamName, 
       tblTeams.teams_ID, 
       tblEmployee.baseRate,
       (SELECT COUNT(tblTeamMembers.employee_ID)  
          FROM  tblTeamMembers 
         WHERE tblTeamMembers.teams_Id = tblTeams.teams_ID) AS countTeamMembers, 
       (tblProducts.sam * tblProduction.totalProduced) / ((SUM(DateDiff('h', Format(tblProduction.startDate, 'short date') + ' ' + tblStartTime.[time], Format(tblProduction.endDate, 'short date') + ' ' + tblEndTime.[time]))) * countTeamMembers) / 60 AS totalTeamMembers
 FROM ((tblTeams 
INNER JOIN (tblProducts 
INNER JOIN ((tblProduction 
INNER JOIN tblTime tblStartTime 
   ON tblProduction.startTime = tblStartTime.time_ID) 
INNER JOIN tblTime tblEndTime 
   ON tblProduction.endTime = tblEndTime.time_ID) 
   ON tblProducts.product_ID = tblProduction.product_ID) 
   ON tblTeams.teams_ID = tblProduction.teams_ID) 
INNER JOIN (tblEmployee 
RIGHT OUTER JOIN tblTeamMembers 
   ON tblEmployee.employee_ID = tblTeamMembers.employee_ID) 
   ON tblTeams.teams_ID = tblTeamMembers.teams_Id)
GROUP BY tblEmployee.employeeLastName + ', ' + tblEmployee.employeeFirstName, 
         tblProducts.sam, 
         tblProduction.totalProduced, 
         tblTeams.teamName, 
         tblTeams.teams_ID, 
         tblEmployee.baseRate
ORDER BY tblEmployee.employeeLastName + ', ' + tblEmployee.employeeFirstName)
SELECT empFullName, 
       totalHours,
       sam, 
       totalProduced, 
       teamName, 
       teams_ID, 
       baseRate,
       AVG(totalTeamMembers)
  FROM clean_it_up
  GROUP BY empFullName, 
		   sam, 
		   totalProduced, 
		   teamName, 
		   teams_ID, 
		   baseRate

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

swilli6926
Starting Member

4 Posts

Posted - 2010-07-13 : 15:30:09

Hi yosiasz,

I used VS2008 query builder originally and pasted into Access Query Builder...

Your t-sql above does not seem to like Access... :(

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-13 : 15:43:19

it most definitely is not :( Can you wrap this into a stored procedure and then call it from access?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

swilli6926
Starting Member

4 Posts

Posted - 2010-07-13 : 15:57:42

Naw, I don't think I can.

Any other suggestions?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-13 : 17:04:29

can you dump it into a table in access and then do the AVG on an Access table?

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources