Need to rollup subtotals to their parent tasks

1

Our database (purchased software) uses an outline number scheme to arrange data. The problem is I can't figure out how to get the subtask data to rollup to the parent tasks (subtotals) and the subtotals to rollup to the project total. A sample outline would look like this:
Outline# Task Name Hours
0 Proj Name
1 Task 1
1.1 Sub task 1 hrs 4
1.2 Sub task 2 hrs 2
1.3 Sub task 3
1.3.1 Sub task 3 sub 1 1 hrs 1.5
1.3.2 Sub task 3 sub 2 hrs 6
2 Task 2
2.1 Sub task 2
2.1.1 Sub task 2 sub 1 hrs 2

I can do an outer join between the project table and the time table to bring in all the tasks and hours, but i can't figure out how to get the subtasks to subtotal at the task level and the tasks to total at the project level.

In the above ex. I need 2.1 to show the 2 hrs, 2 to show the 2 hours, 1.3 to show the 7.5 hrs, 1 to show 13.5 hours, and 0 to show 15.5 hrs. Projects can have 1,000 tasks/subtasks and can go 6 levels deep. i am thinking some type of multiple loop using wildcards but i don't know where to start. Is there some built in function that would handle something like this?

Thanks,

Alan

2

If you'll convert the sample data to useable form -- CREATE TABLE and INSERT statements -- I'll work a query for you.

3

Here is the query output from a small project:
ProjectCode OutlineNum TaskName SumOfStandardHours Chg_Amt
9920-93016 0 CLOSED Other Sales & Marketing 2016
9920-93016 1 Other Mktg - Production
9920-93016 1.1 Other Mktg - Management 631.50 $87,950.50
9920-93016 1.2 Other Mktg - Gen Mkt Planning/Strategy
9920-93016 1.2.1 Other Mktg - General Market Planning 481.00 $77,569.75
9920-93016 1.2.2 IT Services Bus Dev 27.00 $3,510.00
9920-93016 1.2.3 Iowa State 97.00 $16,819.00
9920-93016 1.3 Other Mktg - Proposal/Bid Preparation
9920-93016 1.4 Other Mktg - Advertising 60.50 $7,256.00
9920-93016 2 Other Mktg - Reserve
9920-93016 2.1 Other Mktg - Reserve Management

This is the query that returns the above data on one of our small projects:

[CODE]
SELECT Project.RevisionStatusCode, Task.ProjectCode, Cast(Task.TaskSeqNum AS Int) AS Seq, Task.OutlineNum, Task.TaskUID, Task.TaskName
Into #WBS_1
FROM (Project INNER JOIN Task ON (Project.RevisionNum=Task.RevisionNum) AND (Project.ProjectCode=Task.ProjectCode)) INNER JOIN TaskRule
ON (Task.ProjectCode=TaskRule.ProjectCode) AND (TaskRule.RevisionNum=Task.RevisionNum) AND (Task.TaskUID=TaskRule.TaskUID)
GROUP BY Project.RevisionStatusCode, Task.ProjectCode, Cast(Task.TaskSeqNum As Int), Task.OutlineNum, Task.TaskUID, Task.TaskName

HAVING (((Project.RevisionStatusCode)='A') AND ((Task.ProjectCode)='9920-93016'))
ORDER BY Cast(Task.TaskSeqNum As Int);

SELECT #WBS_1.ProjectCode, #WBS_1.OutlineNum, #WBS_1.TaskName, Sum(Time.StandardHours) AS Hours, SUM(Time.StandardChargeAmt) AS Chg_Amt
FROM Time RIGHT JOIN #WBS_1 ON (Time.ProjectCode = #WBS_1.ProjectCode) AND (Time.TaskUID = #WBS_1.TaskUID)
GROUP BY #WBS_1.ProjectCode, #WBS_1.OutlineNum, #WBS_1.TaskName, #WBS_1.Seq
ORDER BY #WBS_1.Seq;

Drop Table #WBS_1
{/Code] Thanks, Alan

4

Please follow @ScottPletcher's advice and post a crate table script and inserts for a small number of rows representing your data.

5

Sorry, I thought you meant the query I was using to get the data. The code below creates a table that contains the data in my first example :

[CODE]
Create Table Rollup
(TaskName nvarchar(100),
OutlineNum nvarchar(60),
Hours decimal (6,2),
TaskSeq Int)

Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq)
Values ('Proj name', '0', null, 0)
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq)
Values ('Task 1', '1', null, 1)
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq)
Values ('SubTask 1', '1.1', 4, 2)
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq)
Values ('Sub Task 2', '1.2', 2, 3)
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq)
Values ('Sub Task 3', '1.3', null, 4)
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq)
Values ('Sub Task 3 Sub1', '1.3.1', 1.5, 5)
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq)
Values ('Sub Task 3 Sub 2', '1.3.2', 6, 6)
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq)
Values ('Task 2', '2', null, 7)
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq)
Values ('Sub Task 2', '2.1', 2, 8)
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq)
Values ('Sub Task 2 Sub 1', '2.1.1', 2, 9)

Select * from Rollup Order by TaskSeq

[/CODE]

6

Good data, although for testing I added different amounts for the same subtask just to verify everything's rolling up correctly.

All but the overall total is straightforward, as coded below. The code uses an inline tally table, but of course you could use a permanent table instead if you prefer.

The easiest way to get the overall total is probably to put the results below into a temp table, read from that while taking a total of all entries without a '.', i.e., the top-level entries only. Let me know if you want to pursue that.

;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
)
SELECT 
    ca1.TaskName,
    SUM(r.Hours) AS Total_Hours
FROM #Rollup r
CROSS APPLY (
    SELECT LEFT(r.TaskName, t.number - 1) AS TaskName
    FROM cteTally100 t
    WHERE t.number <= LEN(r.TaskName) + 1 AND SUBSTRING(r.TaskName + '.', t.number, 1) = '.'
) AS ca1
WHERE r.TaskName <> '0' AND r.Hours IS NOT NULL
GROUP BY ca1.TaskName
ORDER BY TaskName
7

Thanks,

I will play with see and see how it works for me.

8

This is working against live data, I should be able to use the results to update a temp table with the summary values and have an SSRS report with "parent" task subtotals. Thank you for your help.

9

I need to bug you one more time, I can't seem to add another column to the table. I can select into a temp table and insert the results into my table, but I also need to have monthly rollup totals. When I try to add a column I get two errors :Msg 422, Level 16, State 4, Line 12
Common table expression defined but not used.
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'AS'.

Here is the code with my column & grouping added:

;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.number) AS number
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
)
SELECT
ca1.TaskName, #Rollup.month
SUM(r.Hours) AS Total_Hours
Group By #Rollup.month, ca1,taskname

Into #T1

FROM #Rollup r
CROSS APPLY (
SELECT LEFT(r.TaskName, t.number - 1) AS TaskName, r.month
FROM cteTally100 t
WHERE t.number <= LEN(r.TaskName) + 1 AND SUBSTRING(r.TaskName + '.', t.number, 1) = '.'
) AS ca1
WHERE r.TaskName <> '0' AND r.Hours IS NOT NULL
GROUP BY ca1.TaskName
ORDER BY TaskName

Thanks,

Alan

10

I'm not sure what specifically you're trying to do.

11

The example data above works fine for the total column (I didn't put the month in the example) but the actual report (when rendered in SSRS) has columns for each month of the project(s). Since the month column is null for the summary rows in the rollup query I need to create a version that totals each monthly column. I thought I could just run a copy adding a month column and a group by but when I do I get the errors listed above. The goal is to get it to display in SSRS like the example below. As it is now the outline number 1 row shows the total hours but the Jan & Feb rows are blank.

OutlineNum TaskName Hours TaskSeq Total hours JanHours FebHrs etc.. 1 Task 1 10 1 10 3 7 1.1 Sub Task 1 3 2 3 1 2 1.2 Sub Task 2 7 3 7 2 5

Thanks,

Alan

12

Even though I put the example in a code block the page took out the blank space. In row 1 the 10 should be under the total hours, the 3 under Jan and the 7 under Feb, row 2 should have the 3, 1, 2 under total, Jan, Feb, and row 3 the 7, 2, 5 under total, Jan, Feb.

13

Maybe this will help:

If I alter the initial table to this:

Create Table Rollup
(TaskName nvarchar(100),
OutlineNum nvarchar(60),
Hours decimal (6,2),
TaskSeq Int,
Period nvarchar(7))
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq, Period)
Values ('Proj name', '0', null, 0,null)
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq, Period)
Values ('Task 1', '1', null, 1,null)
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq, Period)
Values ('SubTask 1', '1.1', 4, 2, '2017/01')
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq, Period)
Values ('Sub Task 2', '1.2', 2, 3, '2017/02')
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq, Period)
Values ('Sub Task 3', '1.3', null, 4, '2017/01')
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq, Period)
Values ('Sub Task 3', '1.3', null, 7, '2017/02')
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq, Period)
Values ('Sub Task 3 Sub1', '1.3.1', 1.5, 5, '2017/01')
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq, Period)
Values ('Sub Task 3 Sub 2', '1.3.2', 6, 6, '2017/02')
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq, Period)
Values ('Task 2', '2', null, 7, null)
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq, Period)
Values ('Sub Task 2', '2.1', 2, 8, '2017/01')
Insert Into Rollup (OutlineNum,TaskName, Hours, TaskSeq, Period)
Values ('Sub Task 2 Sub 1', '2.1.1', 2, 9, '2017/01')
Select * from Rollup Order by TaskSeq

--Drop Table Rollup

Select TaskName, OutlineNum, SUM(Hours) As Hours, TaskSeq, Period
from Rollup
Group By TaskName, OutlineNum, Hours, TaskSeq, Period

And I run the query you sent with the additional column of period in SSRS I need to end up with this:

<\CODE>
I may have to run the original code you supplied to get the total column and insert these values into a temp table, then run a version modified to add the summary by month and insert those values into the temp table in order to get SSRS to build the report I need.

Thanks,

Alan