Roll up of % and duration to Parent from Child

• PSB

  SSChampion

  Points: 13195

  More actions

  February 8, 2017 at 5:02 pm

  #319288

  Hi,

  I need to roll up Completed and Duration from child to the respective parent tasks . I have provided an example below .

  CREATE TABLE #RollUp

  (

  SuperTaskID VARCHAR(20),

  SubTaskID VARCHAR(20),

  ID VARCHAR(20),

  TaskName VARCHAR(100),

  Completed INT,

  Duration INT-- in days

  )

  INSERT INTO #RollUp ( SuperTaskID,SubTaskID,ID,TaskName,Completed,Duration )

  SELECT '','IEAKQDDKPFJ','IEAKQDDKPFI','Super Plan 1',10,4

  UNION

  SELECT '','IEAKQDDKJJJ','IEAKQDDKPFI','Super Plan 1',10,4

  UNION

  SELECT 'IEAKQDDKPFI','IEAKQDDKPFK','IEAKQDDKPFJ','Super Plan 1 --Sub Plan 1',5,7

  UNION

  SELECT 'IEAKQDDKPFJ','','IEAKQDDKPFK','Super Plan 1 --Child Plan 1',40,6

  UNION

  SELECT 'IEAKQDDKPFJ','','IEAKQDDKPPP','Super Plan 1 --Child Plan 2',20,1

  UNION

  SELECT 'IEAKQDDKPFI','','IEAKQDDKJJJ','Super Plan 1 --Sub Plan 2',7,9

  UNION

  SELECT '','IEAKQDDKMMM','IEAKQDDKLMJ','Super Plan 2',50,7

  UNION

  SELECT '','IEAKQDDKOOO','IEAKQDDKLMJ','Super Plan 2',50,7

  UNION

  SELECT 'IEAKQDDKLMJ','','IEAKQDDKMMM','Super Plan 2 - Sub Plan 1',50,8

  UNION

  SELECT 'IEAKQDDKLMJ','','IEAKQDDKOOO','Sub Plan 2 - Sub Plan 2',40,4

  SELECT * FROM #RollUp WHERE TaskName LIKE '%Plan 2%'

  -- Desired results

  --Child tasks of SubTaskID (IEAKQDDKPFJ)

  --Roll child complete to Sub tasks which are their parents

  --Complete * Duration/ Total Duration of child tasks of that particular parent =====6*40 + 20*1/6+1=240+20/7

  SELECT 'IEAKQDDKPFI' AS SuperTaskID,'IEAKQDDKPFK' AS SubTaskID,'IEAKQDDKPFJ' AS ID,'Super Plan 1 --Sub Plan 1' AS TaskName,37 AS Completed,7 AS Duration--round to nearest int for duration

  UNION

  SELECT 'IEAKQDDKPFI' AS SuperTaskID,'' AS SubTaskID,'IEAKQDDKJJJ' AS ID,'Super Plan 1 --Sub Plan 2' AS TaskName,7 AS Completed,9 AS Duration

  UNION

  --Finally roll into the super Parent --37*7 + 7*9 / 9+7

  SELECT '' AS SuperTaskID,'IEAKQDDKPFJ' AS SubTaskID,'IEAKQDDKPFI' AS ID,'Super Plan 1' AS TaskName,20 AS Completed,16 AS Duration

  UNION

  SELECT '' AS SuperTaskID,'IEAKQDDKJJJ' AS SubTaskID,'IEAKQDDKPFI' AS ID,'Super Plan 1' AS TaskName,20 AS Completed,16 AS Duration

  --Child tasks has no change

  UNION

  SELECT 'IEAKQDDKPFJ' AS SuperTaskID,'' AS SubTaskID,'IEAKQDDKPFK' AS ID,'Super Plan 1 --Child Plan 1' AS TaskName,40 AS Completed,6 AS Duration

  UNION

  SELECT 'IEAKQDDKPFJ' AS SuperTaskID,'' AS SubTaskID,'IEAKQDDKPPP' AS ID,'Super Plan 1 --Child Plan 2' AS TaskName,20 AS Completed,1 AS Duration

  UNION

  --For Plan 2 --- 400+160 /12

  SELECT '' AS SuperTaskID,'IEAKQDDKOOO' AS SubTaskID,'IEAKQDDKLMJ' AS ID,'Super Plan 2' AS TaskName,47 AS Completed,12 AS Duration

  DROP TABLE #RollUp

  Thanks,
  PSB

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  February 8, 2017 at 5:49 pm

  #1927423

  Sounds like you could use a Windowing function to do this... this isn't right, but should give you some idea.

  SELECT SuperTaskID
, SubTaskID
, ID
, TaskName
, Completed
, SUM(Completed) OVER (PARTITION BY SuperTaskID ORDER BY SubTaskID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TotalCompleted
, SUM(Duration) OVER (PARTITION BY SuperTaskID ORDER BY SubTaskID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TotalDuration
FROM #Rollup
ORDER BY SuperTaskID
, SubTaskID
, TaskName;

• PSB

  SSChampion

  Points: 13195

  More actions

  February 9, 2017 at 5:44 am

  #1927493

  The numbers are not coming in correctly .

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  February 10, 2017 at 12:29 pm

  #1927872

  PSB - Thursday, February 9, 2017 5:44 AM

  The numbers are not coming in correctly .

  I don't see a clear, concise, and OBVIOUS relationship between the records shown.   You'll have to explain that relationship in detail.   The only thing I see is three columns that are identifiers of some kind, but I don't see why any given row would not have a SuperTaskID, so I want to know the EXACT rules that make one row a child and another row a parent.   Please don't leave out even the slightest detail.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Jeff Moden

  SSC Guru

  Points: 1004364

  More actions

  February 10, 2017 at 4:12 pm

  #1927905

  sgmunson - Friday, February 10, 2017 12:29 PM

  PSB - Thursday, February 9, 2017 5:44 AM

  The numbers are not coming in correctly .

  I don't see a clear, concise, and OBVIOUS relationship between the records shown.   You'll have to explain that relationship in detail.   The only thing I see is three columns that are identifiers of some kind, but I don't see why any given row would not have a SuperTaskID, so I want to know the EXACT rules that make one row a child and another row a parent.   Please don't leave out even the slightest detail.

  If you'd like to see what you're getting into, Steve, see the following where I had to wade through this same dataset last month. 😉
  https://www.sqlservercentral.com/Forums/1853399/Parent-child-hierarchy

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004364

  More actions

  February 10, 2017 at 4:22 pm

  #1927907

  PSB - Wednesday, February 8, 2017 5:02 PM

  Hi,

  I need to roll up Completed and Duration from child to the respective parent tasks . I have provided an example below .

  CREATE TABLE #RollUp

  (

  SuperTaskID VARCHAR(20),

  SubTaskID VARCHAR(20),

  ID VARCHAR(20),

  TaskName VARCHAR(100),

  Completed INT,

  Duration INT-- in days

  )

  INSERT INTO #RollUp ( SuperTaskID,SubTaskID,ID,TaskName,Completed,Duration )

  SELECT '','IEAKQDDKPFJ','IEAKQDDKPFI','Super Plan 1',10,4

  UNION

  SELECT '','IEAKQDDKJJJ','IEAKQDDKPFI','Super Plan 1',10,4

  UNION

  SELECT 'IEAKQDDKPFI','IEAKQDDKPFK','IEAKQDDKPFJ','Super Plan 1 --Sub Plan 1',5,7

  UNION

  SELECT 'IEAKQDDKPFJ','','IEAKQDDKPFK','Super Plan 1 --Child Plan 1',40,6

  UNION

  SELECT 'IEAKQDDKPFJ','','IEAKQDDKPPP','Super Plan 1 --Child Plan 2',20,1

  UNION

  SELECT 'IEAKQDDKPFI','','IEAKQDDKJJJ','Super Plan 1 --Sub Plan 2',7,9

  UNION

  SELECT '','IEAKQDDKMMM','IEAKQDDKLMJ','Super Plan 2',50,7

  UNION

  SELECT '','IEAKQDDKOOO','IEAKQDDKLMJ','Super Plan 2',50,7

  UNION

  SELECT 'IEAKQDDKLMJ','','IEAKQDDKMMM','Super Plan 2 - Sub Plan 1',50,8

  UNION

  SELECT 'IEAKQDDKLMJ','','IEAKQDDKOOO','Sub Plan 2 - Sub Plan 2',40,4

  SELECT * FROM #RollUp WHERE TaskName LIKE '%Plan 2%'

  -- Desired results

  --Child tasks of SubTaskID (IEAKQDDKPFJ)

  --Roll child complete to Sub tasks which are their parents

  --Complete * Duration/ Total Duration of child tasks of that particular parent =====6*40 + 20*1/6+1=240+20/7

  SELECT 'IEAKQDDKPFI' AS SuperTaskID,'IEAKQDDKPFK' AS SubTaskID,'IEAKQDDKPFJ' AS ID,'Super Plan 1 --Sub Plan 1' AS TaskName,37 AS Completed,7 AS Duration--round to nearest int for duration

  UNION

  SELECT 'IEAKQDDKPFI' AS SuperTaskID,'' AS SubTaskID,'IEAKQDDKJJJ' AS ID,'Super Plan 1 --Sub Plan 2' AS TaskName,7 AS Completed,9 AS Duration

  UNION

  --Finally roll into the super Parent --37*7 + 7*9 / 9+7

  SELECT '' AS SuperTaskID,'IEAKQDDKPFJ' AS SubTaskID,'IEAKQDDKPFI' AS ID,'Super Plan 1' AS TaskName,20 AS Completed,16 AS Duration

  UNION

  SELECT '' AS SuperTaskID,'IEAKQDDKJJJ' AS SubTaskID,'IEAKQDDKPFI' AS ID,'Super Plan 1' AS TaskName,20 AS Completed,16 AS Duration

  --Child tasks has no change

  UNION

  SELECT 'IEAKQDDKPFJ' AS SuperTaskID,'' AS SubTaskID,'IEAKQDDKPFK' AS ID,'Super Plan 1 --Child Plan 1' AS TaskName,40 AS Completed,6 AS Duration

  UNION

  SELECT 'IEAKQDDKPFJ' AS SuperTaskID,'' AS SubTaskID,'IEAKQDDKPPP' AS ID,'Super Plan 1 --Child Plan 2' AS TaskName,20 AS Completed,1 AS Duration

  UNION

  --For Plan 2 --- 400+160 /12

  SELECT '' AS SuperTaskID,'IEAKQDDKOOO' AS SubTaskID,'IEAKQDDKLMJ' AS ID,'Super Plan 2' AS TaskName,47 AS Completed,12 AS Duration

  DROP TABLE #RollUp

  Thanks,
  PSB

  Hierarchical rollups are always going to be a pain unless you know the "secret" and are able to support it.  Please see the following article for one of the fastest ways there is to do this accurately and consistently.
  http://www.sqlservercentral.com/articles/T-SQL/94570/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  February 11, 2017 at 7:15 am

  #1927942

  Jeff Moden - Friday, February 10, 2017 4:12 PM

  sgmunson - Friday, February 10, 2017 12:29 PM

  PSB - Thursday, February 9, 2017 5:44 AM

  The numbers are not coming in correctly .

  I don't see a clear, concise, and OBVIOUS relationship between the records shown.   You'll have to explain that relationship in detail.   The only thing I see is three columns that are identifiers of some kind, but I don't see why any given row would not have a SuperTaskID, so I want to know the EXACT rules that make one row a child and another row a parent.   Please don't leave out even the slightest detail.

  If you'd like to see what you're getting into, Steve, see the following where I had to wade through this same dataset last month. 😉
  https://www.sqlservercentral.com/Forums/1853399/Parent-child-hierarchy

  Not surprised, Jeff...   Kinda figured as much, as the communication coming from PSB makes it clear that one of two things is likely, and possibly both:

    1.) The OP does not use English as their primary language.
    2.)  The OP does not understand the relationship between the data records and doesn't want to reveal that fact.

  All it took was to look at the first two posts of that other thread, and what I suspected was confirmed...  at least for me anyway...   That's why I always start asking the kinds of questions that if not answered, I don't waste my time.    Thanks for the heads up!

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Jeff Moden

  SSC Guru

  Points: 1004364

  More actions

  February 11, 2017 at 12:22 pm

  #1927956

  I did, finally, get to what the columns were for the parent/child relationship in that previous thread and made it work for the OP (heh... until he changed it).  The "ID" column is useless here and can be ignored.  The SubTaskID column is the Child column and the SuperTaskID column is the Parent column and the typical rCTE solution worked well for his original thread.  I'm pretty sure that the OP didn't design this but has to work with the design.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)