Subtotal/Total based on outline #

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources