February 24, 2017 at 12:59 pm
Hi,
I want to roll Duration field from it's lowest level to it's immediate level parents , then from it's parent's to it's parent's parent based on SuperTaskID .. The lowest level should remain unchanged .
CREATE TABLE #RollUp
(
ID NVARCHAR (100),
SuperTaskID NVARCHAR(100),
Title NVARCHAR(100),
OrigDuration INT,
CalculatedDuration INT,
TaskLevel INT
)
INSERT INTO #RollUp ( ID,SuperTaskID,Title,OrigDuration,CalculatedDuration,TaskLevel )
SELECT 'SuperID1','','Super Task',10,0,0 UNION
SELECT 'SuperID1-SUB1','SuperID1','SUBTASK OF SuperID1',5 ,0,1
UNION
SELECT 'SuperID1-SUB2','SuperID1','SUBTASK2 OF SuperID1',44 ,0,1
UNION
SELECT 'SuperID1-Child1','SuperID1-SUB1','Child Task1 of SuperID1',5 ,0,2
UNION
SELECT 'SuperID1-Child''s Child 1','SuperID1-Child1','Child''s Child Task1 of SuperID1',2 ,0,3
UNION
SELECT 'SuperID1-Child''s Child''s Child 1', 'SuperID1-Child''s Child 1','Level 4 Child Task1 of SuperID1',7 ,0,4
UNION
SELECT 'SuperID1-Child''s Child''s Child 2', 'SuperID1-Child''s Child 1','Level 4 Child Task2 of SuperID1',3 ,0,4
UNION
SELECT 'SuperID1-Child''s Child''s Child Child''s 1','SuperID1-Child''s Child''s Child 1','Level 5 Child Task1 of SuperID1',5 ,0,5
UNION
SELECT 'SuperID1-Child''s Child''s Child Child''s 2','SuperID1-Child''s Child''s Child 1','Level 5 Child Task2 of SuperID1',5 ,0,5
UNION
SELECT 'SuperID1-Child2','SuperID1-SUB1','Child Task 2 of SuperID1',15,0,3
UNION
SELECT 'SuperID1-Child2','SuperID1-SUB1','Child Task 2 of SuperID1',15,0,3
UNION
SELECT 'SuperID2','','Super Task',66,0,0 UNION
SELECT 'SuperID2-SUB1','SuperID2','SUBTASK OF SuperID2',19 ,0,1 UNION
SELECT 'SuperID2-SUB2','SuperID2','SUBTAS2K OF SuperID2',33 ,0,1 UNION
SELECT 'SuperID2-Child1','SuperID2-SUB1','Child Task1 of SuperID2',5 ,0,2 UNION
SELECT 'SuperID2-Child''s Child 1','SuperID2-Child1','Child''s Child Task1 of SuperID2',2,0,3
SELECT * FROM #RollUp WHERE ID LIKE 'SuperID1%' ORDER BY 6
-- Desired Result
SELECT 'SuperID1','','Super Task',10,72 AS CalculatedDuration ,0 UNION --28 + 44
SELECT 'SuperID1-SUB2','SuperID1','SUBTASK2 OF SuperID1',44 ,0 AS CalculatedDuration,1 UNION
SELECT 'SuperID1-SUB1','SuperID1','SUBTASK OF SuperID1',5 ,28 AS CalculatedDuration,1 --13 + 15 (28)
UNION
SELECT 'SuperID1-Child1','SuperID1-SUB1','Child Task1 of SuperID1',5 ,13 AS CalculatedDuration,2 -- 13
UNION
SELECT 'SuperID1-Child2','SuperID1-SUB1','Child Task 2 of SuperID1',15,0 AS CalculatedDuration,3
UNION
SELECT 'SuperID1-Child''s Child 1','SuperID1-Child1','Child''s Child Task1 of SuperID1',2 ,13 AS CalculatedDuration,3 --10 + 3
UNION
SELECT 'SuperID1-Child''s Child''s Child 1', 'SuperID1-Child''s Child 1','Level 4 Child Task1 of SuperID1',7 ,10 AS CalculatedDuration,4 -- 5 + 5 (10)
UNION
SELECT 'SuperID1-Child''s Child''s Child 2', 'SuperID1-Child''s Child 1','Level 4 Child Task2 of SuperID1',3 ,0 AS CalculatedDuration,4
UNION
SELECT 'SuperID1-Child''s Child''s Child Child''s 1','SuperID1-Child''s Child''s Child 1','Level 5 Child Task1 of SuperID1',5 ,0 AS CalculatedDuration,5 -- Sum 5 and 5 from Level 5s
UNION
SELECT 'SuperID1-Child''s Child''s Child Child''s 2','SuperID1-Child''s Child''s Child 1','Level 5 Child Task2 of SuperID1',5 ,0 AS CalculatedDuration,5
UNION
SELECT 'SuperID1-Child2','SuperID1-SUB1','Child Task 2 of SuperID1',15,0 AS CalculatedDuration,3
UNION
SELECT 'SuperID2','','Super Task',66,35 AS CalculatedDuration,0 UNION --2 + 33
SELECT 'SuperID2-SUB1','SuperID2','SUBTASK OF SuperID2',19 ,2 AS CalculatedDuration,1 UNION --2 from SuperID2-Child1
SELECT 'SuperID2-SUB2','SuperID2','SUBTAS2K OF SuperID2',33 ,0 AS CalculatedDuration,1 UNION
SELECT 'SuperID2-Child1','SuperID2-SUB1','Child Task1 of SuperID2',5 ,2 AS CalculatedDuration,2 UNION-- two from 'SuperID2-Child''s Child 1'
SELECT 'SuperID2-Child''s Child 1','SuperID2-Child1','Child''s Child Task1 of SuperID2',2,0 AS CalculatedDuration,3
DROP TABLE #RollUp
Thanks,
PSB
February 24, 2017 at 1:12 pm
I recommend you have a look at this article by Wayne Sheffield
http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 24, 2017 at 1:19 pm
It would be helpful to include the results you'd like to see. Preferably as a table insert:create table results (id, title, level)
insert results
values (1, 'test', 1)
, ( 2, 'new test', 4)
February 24, 2017 at 1:24 pm
With #Results table :
CREATE TABLE #RollUp
(
ID NVARCHAR (100),
SuperTaskID NVARCHAR(100),
Title NVARCHAR(100),
OrigDuration INT,
CalculatedDuration INT,
TaskLevel INT
)
CREATE TABLE #Results
(
ID NVARCHAR (100),
SuperTaskID NVARCHAR(100),
Title NVARCHAR(100),
OrigDuration INT,
CalculatedDuration INT,
TaskLevel INT
)
INSERT INTO #RollUp ( ID,SuperTaskID,Title,OrigDuration,CalculatedDuration,TaskLevel )
SELECT 'SuperID1','','Super Task',10,0,0 UNION
SELECT 'SuperID1-SUB1','SuperID1','SUBTASK OF SuperID1',5 ,0,1
UNION
SELECT 'SuperID1-SUB2','SuperID1','SUBTASK2 OF SuperID1',44 ,0,1
UNION
SELECT 'SuperID1-Child1','SuperID1-SUB1','Child Task1 of SuperID1',5 ,0,2
UNION
SELECT 'SuperID1-Child''s Child 1','SuperID1-Child1','Child''s Child Task1 of SuperID1',2 ,0,3
UNION
SELECT 'SuperID1-Child''s Child''s Child 1', 'SuperID1-Child''s Child 1','Level 4 Child Task1 of SuperID1',7 ,0,4
UNION
SELECT 'SuperID1-Child''s Child''s Child 2', 'SuperID1-Child''s Child 1','Level 4 Child Task2 of SuperID1',3 ,0,4
UNION
SELECT 'SuperID1-Child''s Child''s Child Child''s 1','SuperID1-Child''s Child''s Child 1','Level 5 Child Task1 of SuperID1',5 ,0,5
UNION
SELECT 'SuperID1-Child''s Child''s Child Child''s 2','SuperID1-Child''s Child''s Child 1','Level 5 Child Task2 of SuperID1',5 ,0,5
UNION
SELECT 'SuperID1-Child2','SuperID1-SUB1','Child Task 2 of SuperID1',15,0,3
UNION
SELECT 'SuperID1-Child2','SuperID1-SUB1','Child Task 2 of SuperID1',15,0,3
UNION
SELECT 'SuperID2','','Super Task',66,0,0 UNION
SELECT 'SuperID2-SUB1','SuperID2','SUBTASK OF SuperID2',19 ,0,1 UNION
SELECT 'SuperID2-SUB2','SuperID2','SUBTAS2K OF SuperID2',33 ,0,1 UNION
SELECT 'SuperID2-Child1','SuperID2-SUB1','Child Task1 of SuperID2',5 ,0,2 UNION
SELECT 'SuperID2-Child''s Child 1','SuperID2-Child1','Child''s Child Task1 of SuperID2',2,0,3
SELECT * FROM #RollUp WHERE ID LIKE 'SuperID1%' ORDER BY 6
-- Desired Result
INSERT INTO #Results ( ID,SuperTaskID,Title,OrigDuration,CalculatedDuration,TaskLevel )
SELECT 'SuperID1','','Super Task',10,72 AS CalculatedDuration ,0 UNION --28 + 44
SELECT 'SuperID1-SUB2','SuperID1','SUBTASK2 OF SuperID1',44 ,0 AS CalculatedDuration,1 UNION
SELECT 'SuperID1-SUB1','SuperID1','SUBTASK OF SuperID1',5 ,28 AS CalculatedDuration,1 --13 + 15 (28)
UNION
SELECT 'SuperID1-Child1','SuperID1-SUB1','Child Task1 of SuperID1',5 ,13 AS CalculatedDuration,2 -- 13
UNION
SELECT 'SuperID1-Child2','SuperID1-SUB1','Child Task 2 of SuperID1',15,0 AS CalculatedDuration,3
UNION
SELECT 'SuperID1-Child''s Child 1','SuperID1-Child1','Child''s Child Task1 of SuperID1',2 ,13 AS CalculatedDuration,3 --10 + 3
UNION
SELECT 'SuperID1-Child''s Child''s Child 1', 'SuperID1-Child''s Child 1','Level 4 Child Task1 of SuperID1',7 ,10 AS CalculatedDuration,4 -- 5 + 5 (10)
UNION
SELECT 'SuperID1-Child''s Child''s Child 2', 'SuperID1-Child''s Child 1','Level 4 Child Task2 of SuperID1',3 ,0 AS CalculatedDuration,4
UNION
SELECT 'SuperID1-Child''s Child''s Child Child''s 1','SuperID1-Child''s Child''s Child 1','Level 5 Child Task1 of SuperID1',5 ,0 AS CalculatedDuration,5
UNION
SELECT 'SuperID1-Child''s Child''s Child Child''s 2','SuperID1-Child''s Child''s Child 1','Level 5 Child Task2 of SuperID1',5 ,0 AS CalculatedDuration,5
UNION
SELECT 'SuperID1-Child2','SuperID1-SUB1','Child Task 2 of SuperID1',15,0 AS CalculatedDuration,3
UNION
SELECT 'SuperID2','','Super Task',66,35 AS CalculatedDuration,0 UNION --2 + 33
SELECT 'SuperID2-SUB1','SuperID2','SUBTASK OF SuperID2',19 ,2 AS CalculatedDuration,1 UNION --2 from SuperID2-Child1
SELECT 'SuperID2-SUB2','SuperID2','SUBTAS2K OF SuperID2',33 ,0 AS CalculatedDuration,1 UNION
SELECT 'SuperID2-Child1','SuperID2-SUB1','Child Task1 of SuperID2',5 ,2 AS CalculatedDuration,2 UNION-- two from 'SuperID2-Child''s Child 1'
SELECT 'SuperID2-Child''s Child 1','SuperID2-Child1','Child''s Child Task1 of SuperID2',2,0 AS CalculatedDuration,3
SELECT * FROM #Results
DROP TABLE #RollUp
DROP TABLE #Results
February 26, 2017 at 8:28 pm
Any help will be appreciated..
February 26, 2017 at 11:00 pm
PSB - Sunday, February 26, 2017 8:28 PMAny help will be appreciated..
For your entire series of questions on this particular table, you just need to understand that you have a "simple' Adjacency List and the two articles below will turn you into a hierarchical wizard. The second article is most relevant to your current problem.
http://www.sqlservercentral.com/articles/Hierarchy/94040/
http://www.sqlservercentral.com/articles/T-SQL/94570/
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2017 at 8:16 am
PSB - Sunday, February 26, 2017 8:28 PMAny help will be appreciated..
We need to redo your schema as it makes no sense. By definition, a table has to have a key, but there is no way this stuff can have a key, because everything is nullable. There is no such thing as a generic “id†in RDBMS. Why do you think that I hundred Chinese characters is a good design for identifiers?
ROLLUP is both a verb and a reserved word in SQL. It is not an entity or the valid name of any table. Likewise RESULT is too generic to ever be a valid entity name. However, more important, the effective two tables are redundant. Please read what Chris Date and Dave McGovern have to say about what a horrible thing it is.
Why are you still using the old Sybase notation for insertions? Microsoft has had ANSI/ISO standard syntax for years.
You need to get a copy of my “Trees and Hierarchies in SQL for Smarties†(ISBN 978–1–55860–920–4) and read chapter 4.7 where I work this problem out in detail. No, I am not going to post a few thousand words for you on a forum.
To get you started look up the nested set model for trees; what you are doing is called an adjacency list model, and it is the worst way to model any sort of hierarchy in SQL.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply