Summarize work by Department - 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)

Summarize work by Department

Author

Topic

JohnBGood
Starting Member

48 Posts

Posted - 2010-02-28 : 11:52:52

Table 1 contains hierarchical info about the company

GroupID, ParentGroupID,GroupName,FullGroupName
1,null,CEO,CEO
2, 1, CIO, CEO.CIO
3, 2, VP of Infrastructure, CEO.CIO.VP of Infrastructure
4,2, VP of Applications,CEO.CIO.VP of Applications
5, 3, Server Group Manager,CEO.CIO.VP of Infrastructure.Server Group Manager
6, 3, Networking Group Manager, CEO.CIO.VP of Infrastructure.Networking Group Manager
7, 6, Server Engineer,CEO.CIO.VP of Infrastructure.Server Group Manager.Server Engineer

Table 2 maps Tasks to resource

TaskID, ResourceID, FullGroupName
1,1,VP of Applications.Networking Group Manager.Server Engineer

Table 3 has detailed daily work schedules
TaskID, TimeByDay,Work
1,2/1/2010,8
1,2,2/2010,8
1,2/3/2010,8

I want to sum the work by department

GroupID, ParentGroupID, Sum(Work)
-- for example
1,null,24
2, 1, 24
3, 2, 24
4,2, 0
5, 3, 24
6, 3, 0
7, 6, 24

Guidance appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-28 : 12:14:48

[code]SELECT t1.GroupID,t1.ParentGroupID,SUM(Work) AS WorkTime
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.ResourceID = t1.GroupID
INNER JOIN Table3 t3
ON t3.TaskID=t2.TaskID
GROUP BY t1.GroupID,t1.ParentGroupID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources