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
 General SQL Server Forums
 New to SQL Server Programming
 Subtotal/Total based on outline #

Author  Topic 

Alan1018
Starting Member

13 Posts

Posted - 2014-08-29 : 09:22:08
Our project management database uses outline number and depth to order the WBS (work breakdown structure) with 0 as the project name (no child tasks) and continuing with 1, 1.1 1.2, 1.2.1, etc. I need to roll up the hours on the subtasks then roll the subtasks up and up and up to the project total. Here is a simple WBS example, the projects can go 10 subtasks deep and there can be over 100 child tasks on a subtask:

Project OutlineNum Depth TaskName
12345 0 1 Proj Name Total
12345 1 2 Labor Total of 1.x
12345 1.1 3 Task 1
12345 1.2 3 Task 2
12345 1.3 3 Task 3 Total of 1.3.x
12345 1.3.1 4 Task 3.1
12345 1.3.2 4 Task 3.2
12345 1.3.3 4 Task 3.3
12345 1.3.4 4 Task 3.4
12345 1.3.5 4 Task 3.5
12345 1.3.6 4 Task 3.6
12345 1.3.7 4 Task 3.7
12345 1.3.8 4 Task 3.8
12345 1.4 3 Task 4 Total of 1.4.x
12345 1.4.1 4 Task 4.1
12345 1.4.2 4 Task 4.2
12345 1.4.3 4 Task 4.3
12345 1.4.4 4 Task 4.4
12345 1.4.5 4 Task 4.5 Total 1.4.5.x
12345 1.4.5.1 5 Task 4.5.1
12345 1.4.5.2 5 Task 4.5.2
12345 1.4.5.3 5 Task 4.5.3
12345 1.4.5.4 5 Task 4.5.4
12345 1.5 3 Task 1.5
12345 1.6 3 Task 1.6 Total 1.6.x
12345 1.6.1 4 Task 1.6.1
12345 2 2 Task 2 Total 2.x
12345 2.1 3 Task 2.1
12345 2.2 3 Task 2.2
12345 3 2 Task 3 Total 3.x
12345 3.1 3 Task 3.1 Total 3.1.x
12345 3.1.1 4 Task 3.1.1

I thought about a loop counter based on outline and depth but I think that will be overly complicated, long, and very difficult. If anyone has any suggestions I would be glad for the help.
Thanks,
Alan


Thanks,
Alan

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-29 : 09:43:50
Where are the hours? I don't see them in your sample data.

BTW with a properly-defined table in SQL, you should be able to get what you want using GROUP WITH ROLLUP
Go to Top of Page

Alan1018
Starting Member

13 Posts

Posted - 2014-08-29 : 10:03:30
The hours would be in the Total column I didn't include hours, I only put the "subtotal formulas" is the column.

Thanks,
Alan
Go to Top of Page

Alan1018
Starting Member

13 Posts

Posted - 2014-08-29 : 12:32:48
Here is the code I tried:

SELECT Task.OutlineNum, SUM(Time.StandardHours) AS Hours
FROM Project INNER JOIN
Task ON Project.ProjectCode = Task.ProjectCode AND Project.RevisionNum = Task.RevisionNum LEFT OUTER JOIN
Time ON Task.ProjectCode = Time.ProjectCode AND Task.TaskUID = Time.TaskUID
WHERE (Project.RevisionStatusCode = N'A') AND (Project.ProjectCode = '12345')
GROUP BY Task.OutlineNum WITH ROLLUP


This is what I should have gotten:

Project OutlineNum Hours
12345 0 1190.5
12345 1 1190.5
12345 1.1 162.25
12345 1.2 48.25
12345 1.3 83.75
12345 1.3.1 18
12345 1.3.2 0.25
12345 1.3.3 0.25
12345 1.3.4 0.25
12345 1.3.5 17.5
12345 1.3.6 19
12345 1.3.7
12345 1.3.8 28.5
12345 1.4 174.5
12345 1.4.1 7.5
12345 1.4.2 6
12345 1.4.3 21.5
12345 1.4.4 47
12345 1.4.5
12345 1.4.6
12345 1.4.7 10
12345 1.4.8 30
12345 1.4.8.1 10
12345 1.4.8.2 20
12345 1.4.8.3
12345 1.4.8.4
12345 1.4.9 14
12345 1.4.9.1 6
12345 1.4.9.2 8
12345 1.4.9.3
12345 1.4.9.4
12345 1.4.10 38.5
12345 1.4.10.1 8
12345 1.4.10.2 29.5
12345 1.4.10.3
12345 1.4.10.4 1
12345 1.5 22
12345 1.6 241.25
12345 1.6.1 9.75
12345 1.6.2 42.75
12345 1.6.3 69.5
12345 1.6.4 30.5
12345 1.6.5 8
12345 1.6.6 80.75
12345 1.7 155
12345 1.7.1 38
12345 1.7.2 26.5
12345 1.7.3 40.75
12345 1.7.4 47
12345 1.7.5 2.75
12345 1.8 153.5
12345 1.8.1 12.25
12345 1.8.2 12
12345 1.8.3 21.25
12345 1.8.4 38
12345 1.8.5 21
12345 1.8.6 18
12345 1.8.7 13
12345 1.8.8 18
12345 1.9 150
12345 1.9.1
12345 1.9.2 59.5
12345 1.9.3
12345 1.9.4 77.25
12345 1.9.4.1 26.5
12345 1.9.4.2 50.75
12345 1.9.4.3
12345 1.9.4.4 0
12345 1.9.4.4. 1
12345 1.9.4.5 17.25
12345 1.9.5 13.25
12345 2 0
12345 2.1
12345 2.2
12345 3 0
12345 3.1 0
12345 3.1.1

This is what I got:

OutlineNum Hours
0 NULL
1 NULL
1.1 162.25
1.2 48.25
1.3 NULL
1.3.1 18.00
1.3.2 0.25
1.3.3 0.25
1.3.4 0.25
1.3.5 17.50
1.3.6 19.00
1.3.7 NULL
1.3.8 28.50
1.4 NULL
1.4.1 7.50
1.4.10 NULL
1.4.10.1 8.00
1.4.10.2 29.50
1.4.10.3 NULL
1.4.10.4 1.00
1.4.2 6.00
1.4.3 21.50
1.4.4 47.00
1.4.5 NULL
1.4.6 NULL
1.4.7 10.00
1.4.8 NULL
1.4.8.1 10.00
1.4.8.2 20.00
1.4.8.3 NULL
1.4.8.4 NULL
1.4.9 NULL
1.4.9.1 6.00
1.4.9.2 8.00
1.4.9.3 NULL
1.4.9.4 NULL
1.5 22.00
1.6 NULL
1.6.1 9.75
1.6.2 42.75
1.6.3 69.50
1.6.4 30.50
1.6.5 8.00
1.6.6 80.75
1.7 NULL
1.7.1 38.00
1.7.2 26.50
1.7.3 40.75
1.7.4 47.00
1.7.5 2.75
1.8 NULL
1.8.1 12.25
1.8.2 12.00
1.8.3 21.25
1.8.4 38.00
1.8.5 21.00
1.8.6 18.00
1.8.7 13.00
1.8.8 18.00
1.9 NULL
1.9.1 NULL
1.9.2 59.50
1.9.3 NULL
1.9.4 NULL
1.9.4.1 26.50
1.9.4.2 50.75
1.9.4.3 NULL
1.9.4.4 NULL
1.9.4.4.1 0.00
1.9.4.5 17.25
1.9.5 13.25
2 NULL
2.1 NULL
2.2 NULL
3 NULL
3.1 NULL
3.1.1 NULL
NULL 1207.75


Thanks,
Alan
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-29 : 14:16:45
OK. so far so good! Now you need to break up the OutlineNum column into four columns (e.g. OutlineNum1, OutlineNum2, ...) and put them in your rollup:


...with rollup (OutlineNum1, OutineNum2, ...)


More info here:

http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx
Go to Top of Page
   

- Advertisement -