Parent child hierarchy

• PSB

  SSChampion

  Points: 13195

  More actions

  January 24, 2017 at 9:00 am

  #318165

  Hi,
  　I want the TaskName field to show Hierarchy in the order with further indentation of the child .
        + SuperTask
         -- SubTask
          ----ChildTasks
  

  CREATE TABLE #ParentChildHierarchy

  (

  SuperTaskID NVARCHAR (20),

  TaskID NVARCHAR (20),

  ChildID NVARCHAR (20),

  TaskName NVARCHAR (4000),

  TaskStart DATETIME,

  TaskEnd DATETIME

  )

  INSERT INTO #ParentChildHierarchy (SuperTaskID , TaskID , ChildID , TaskName , TaskStart , TaskEnd )

  --ParentTasks

  SELECT 'IEA6IKQDVRK6M','IEA6IKQDVRK6M',NULL,'+ DP Alpha release',NULL,'2017-01-06 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5N','IEA6IKQDVRK5N',NULL,'+ DP Committee Gate Final Gate','2016-08-01 09:00:00.000','2018-04-05 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5N','IEA6IKQDVRK55',NULL,'--Handover Day','2016-11-15 09:00:00.000','2016-11-15 17:00:00.000'

  --SubTasks

  UNION

  SELECT 'IEA6IKQDVRK5N','IEA6IKQDVRK5P','IEA6IKQDVRK52','--Governance Committee','2017-01-05 09:00:00.000','2018-01-08 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5N','IEA6IKQDVRK5P','IEA6IKQDVRK5S','--Governance Committee','2017-01-05 09:00:00.000','2018-01-08 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5N','IEA6IKQDVRK5P','IEA6IKQDVRL3S','--Governance Committee','2017-01-05 09:00:00.000','2018-01-08 17:00:00.000'

  ---Childs

  UNION

  SELECT 'IEA6IKQDVRK5P','IEA6IKQDVRK52',NULL,'--Agree major changes in scope etc','2016-11-04 09:00:00.000','2017-10-31 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5P','IEA6IKQDVRK5S',NULL,'--Continue with team','2016-11-04 09:00:00.000','2017-10-31 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5P','IEA6IKQDVRL3S',NULL,'--Progress goals','2016-11-04 09:00:00.000','2017-10-31 17:00:00.000'

  SELECT * FROM #ParentChildHierarchy

  --Desired results

  SELECT 'IEA6IKQDVRK6M' AS SuperTaskID,'IEA6IKQDVRK6M' AS TaskID,'+ DP Alpha release' AS TaskName,NULL AS TaskStart,'2017-01-06 17:00:00.000' AS TaskEnd

  UNION

  SELECT 'IEA6IKQDVRK5N' AS SuperTaskID,'IEA6IKQDVRK5N' AS TaskID,'+ DP Committee Gate Final Gate' AS TaskName,'2016-08-01 09:00:00.000' AS TaskStart,'2018-04-05 17:00:00.000' AS TaskEnd

  UNION

  SELECT 'IEA6IKQDVRK5N' AS SuperTaskID,'IEA6IKQDVRK5P' AS TaskID,'--Governance Committee' AS TaskName,'2017-01-05 09:00:00.000' AS TaskStart,'2018-01-08 17:00:00.000' AS TaskEnd

  UNION

  SELECT 'IEA6IKQDVRK5P' AS SuperTaskID,'IEA6IKQDVRK52' AS TaskID,'----Agree major changes in scope etc' AS TaskName,'2016-11-04 09:00:00.000' AS TaskStart,'2017-10-31 17:00:00.000' AS TaskEnd

  UNION

  SELECT 'IEA6IKQDVRK5P' AS SuperTaskID,'IEA6IKQDVRK5S' AS TaskID,'----Continue with team' AS TaskName,'2016-11-04 09:00:00.000' AS TaskStart,'2017-10-31 17:00:00.000' AS TaskEnd

  UNION

  SELECT 'IEA6IKQDVRK5P' AS SuperTaskID,'IEA6IKQDVRL3S' AS TaskID,'----Progress goals' AS TaskName,'2016-11-04 09:00:00.000' AS TaskStart,'2017-10-31 17:00:00.000' AS TaskEnd

  DROP TABLE #ParentChildHierarchy

  　Thanks,
  PSB

• PSB

  SSChampion

  Points: 13195

  More actions

  January 25, 2017 at 5:14 am

  #1924817

  Can anyone please help ?

• DamianC

  SSCrazy Eights

  Points: 8023

  More actions

  January 25, 2017 at 8:07 am

  #1924863

  Think a recursive cte would help here
  Take a look at something like (which also handles indentation) : http://sqlblog.com/blogs/john_paul_cook/archive/2009/10/03/displaying-hierarchical-data-indenting-the-output.aspx

  - Damian

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  January 25, 2017 at 8:14 am

  #1924867

  PSB - Wednesday, January 25, 2017 5:14 AM

  Can anyone please help ?

  Yes.  I'll be back in about 15 minutes.  The problem with this hierarchy is that someone denormalized it because they thought it would be easier.  As a result, they have "loops" built into it and a whole lot of unnecessary duplication.  It would have been much better if the maintained it as a more simple "Adjacency List" where each node is only "aware" of its parent.  It turns out that what's being called a "ChildID" in this table is virtually useless.

  And God help you if they ever see the need to add another level.  😉

  --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: 1004356

  More actions

  January 25, 2017 at 8:34 am

  #1924878

  Here's the solution you asked for.  It also correctly places the "Handover Day".  The correct solution would be to turn the table into a true Adjacency List for ease of maintenance and update it for Nested Sets traversals for performance.  Please see the article at the following link for that.
  http://www.sqlservercentral.com/articles/Hierarchy/94040/
  
  
   WITH cteTraverse AS
( --=== Find the root nodes and start the "SortPath".
 SELECT  SuperTaskID
        ,TaskID
        ,TaskName   = SUBSTRING(TaskName,3,4000)
        ,TaskStart
        ,TaskEnd
        ,hLevel     = 0 --We will use this to determine "indentation"
        ,SortPath   = CONVERT(VARCHAR(8000),'|' + SuperTaskID + '|')
   FROM #ParentChildHierarchy
  WHERE SuperTaskID = TaskID
  UNION ALL
  --=== Traverse the hierarchy and build the outputs as we go.
     -- This is the "recursive" part of this rCTE (Recursive CTE).
 SELECT  tbl.SuperTaskID
        ,tbl.TaskID
        ,TaskName       = SUBSTRING(tbl.TaskName,3,4000)
        ,tbl.TaskStart
        ,tbl.TaskEnd
        ,hLevel         = cte.hLevel + 1
        ,SortPath       = CONVERT(VARCHAR(8000),cte.SortPath + tbl.TaskID + '|')
   FROM cteTraverse           cte
   JOIN #ParentChildHierarchy tbl ON tbl.SuperTaskID = cte.TaskID 
  WHERE cte.SortPath NOT LIKE '%|' + tbl.TaskID + '|%' /*This breaks the "loops" built into the hierarchy*/
) --=== Produce the sorted, indented output
 SELECT DISTINCT /*The DISTINCT is necessary to get rid of the duplication*/
         SuperTaskID
        ,TaskID
        ,TaskName = CASE
                    WHEN hLevel = 0 THEN '+ ' + TaskName
                    ELSE REPLICATE('--',hLevel) + TaskName
                    END
        ,TaskStart
        ,TaskEnd
        ,hLevel     -- Comment out if you do not want to see this.
        ,SortPath   -- Comment out if you do not want to see this.
   FROM cteTraverse t
  ORDER BY t.SortPath
;


  --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)
  

• PSB

  SSChampion

  Points: 13195

  More actions

  January 25, 2017 at 8:45 am

  #1924886

  Jeff Moden - Wednesday, January 25, 2017 8:34 AM

  Here's the solution you asked for.  It also correctly places the "Handover Day".  The correct solution would be to turn the table into a true Adjacency List for ease of maintenance and update it for Nested Sets traversals for performance.  Please see the article at the following link for that.
  http://www.sqlservercentral.com/articles/Hierarchy/94040/
  
  
   WITH cteTraverse AS
( --=== Find the root nodes and start the "SortPath".
 SELECT  SuperTaskID
        ,TaskID
        ,TaskName   = SUBSTRING(TaskName,3,4000)
        ,TaskStart
        ,TaskEnd
        ,hLevel     = 0 --We will use this to determine "indentation"
        ,SortPath   = CONVERT(VARCHAR(8000),'|' + SuperTaskID + '|')
   FROM #ParentChildHierarchy
  WHERE SuperTaskID = TaskID
  UNION ALL
  --=== Traverse the hierarchy and build the outputs as we go.
     -- This is the "recursive" part of this rCTE (Recursive CTE).
 SELECT  tbl.SuperTaskID
        ,tbl.TaskID
        ,TaskName       = SUBSTRING(tbl.TaskName,3,4000)
        ,tbl.TaskStart
        ,tbl.TaskEnd
        ,hLevel         = cte.hLevel + 1
        ,SortPath       = CONVERT(VARCHAR(8000),cte.SortPath + tbl.TaskID + '|')
   FROM cteTraverse           cte
   JOIN #ParentChildHierarchy tbl ON tbl.SuperTaskID = cte.TaskID 
  WHERE cte.SortPath NOT LIKE '%|' + tbl.TaskID + '|%' /*This breaks the "loops" built into the hierarchy*/
) --=== Produce the sorted, indented output
 SELECT DISTINCT /*The DISTINCT is necessary to get rid of the duplication*/
         SuperTaskID
        ,TaskID
        ,TaskName = CASE
                    WHEN hLevel = 0 THEN '+ ' + TaskName
                    ELSE REPLICATE('--',hLevel) + TaskName
                    END
        ,TaskStart
        ,TaskEnd
        ,hLevel     -- Comment out if you do not want to see this.
        ,SortPath   -- Comment out if you do not want to see this.
   FROM cteTraverse t
  ORDER BY t.SortPath
;


  Thanks but the code doesn't return any result set.

• PSB

  SSChampion

  Points: 13195

  More actions

  January 25, 2017 at 8:46 am

  #1924887

  Jeff Moden - Wednesday, January 25, 2017 8:14 AM

  PSB - Wednesday, January 25, 2017 5:14 AM

  Can anyone please help ?

  Yes.  I'll be back in about 15 minutes.  The problem with this hierarchy is that someone denormalized it because they thought it would be easier.  As a result, they have "loops" built into it and a whole lot of unnecessary duplication.  It would have been much better if the maintained it as a more simple "Adjacency List" where each node is only "aware" of its parent.  It turns out that what's being called a "ChildID" in this table is virtually useless.

  And God help you if they ever see the need to add another level.  😉

  Yes, You are right . There is no child ids in the original table . But I do have SuperTaskID,SubTaskID and TaskIDs

  CREATE TABLE #ParentChildHierarchy

  (

  SuperTaskID NVARCHAR (20),

  TaskID NVARCHAR (20),

  SubTaskID NVARCHAR (20),

  TaskName NVARCHAR (4000),

  TaskStart DATETIME,

  TaskEnd DATETIME

  )

  INSERT INTO #ParentChildHierarchy (SuperTaskID , TaskID , SubTaskID , TaskName , TaskStart , TaskEnd )

  --ParentTasks

  SELECT NULL, 'IEA6IKQDVRK6M', NULL, 'DP Alpha release', NULL, '2017-01-06 17:00:00.000'

  UNION

  --SubTasks

  SELECT NULL, 'IEA6IKQDVRK5N', 'IEA6IKQDVRK5P','DP Committee Gate Final Gate', '2016-08-01 09:00:00.000', '2018-04-05 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5N', 'IEA6IKQDVRK55', NULL, 'Handover Day', '2016-11-15 09:00:00.000', '2016-11-15 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5N','IEA6IKQDVRK5P','IEA6IKQDVRK52','Governance Committee','2017-01-05 09:00:00.000','2018-01-08 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5N','IEA6IKQDVRK5P','IEA6IKQDVRK5S','Governance Committee','2017-01-05 09:00:00.000','2018-01-08 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5N','IEA6IKQDVRK5P','IEA6IKQDVRK3S','Governance Committee','2017-01-05 09:00:00.000','2018-01-08 17:00:00.000'

  ---Childs

  UNION

  SELECT 'IEA6IKQDVRK5P', 'IEA6IKQDVRK52' ,NULL,'Agree major changes in scope etc', '2016-11-04 09:00:00.000', '2017-10-31 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5P', 'IEA6IKQDVRK5S', NULL,'Continue with team', '2016-11-04 09:00:00.000', '2017-10-31 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5P', 'IEA6IKQDVRL3S', NULL ,'Progress goals', '2016-11-04 09:00:00.000', '2017-10-31 17:00:00.000'

  I want to see something like this .

  --Desired goals

  '+ DP Alpha release'

  '+ DP Committee Gate Final Gate'

  '-->Governance Committee'

  '-------->Agree major changes in scope etc'

  '-------->Continue with team'

  '-------->Progress goals'

  '-->Handover Day'

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  January 25, 2017 at 9:54 am

  #1924907

  Jeff Moden - Wednesday, January 25, 2017 8:34 AM

  Here's the solution you asked for.  It also correctly places the "Handover Day".  The correct solution would be to turn the table into a true Adjacency List for ease of maintenance and update it for Nested Sets traversals for performance.  Please see the article at the following link for that.
  http://www.sqlservercentral.com/articles/Hierarchy/94040/
  
  
   WITH cteTraverse AS
( --=== Find the root nodes and start the "SortPath".
 SELECT  SuperTaskID
        ,TaskID
        ,TaskName   = SUBSTRING(TaskName,3,4000)
        ,TaskStart
        ,TaskEnd
        ,hLevel     = 0 --We will use this to determine "indentation"
        ,SortPath   = CONVERT(VARCHAR(8000),'|' + SuperTaskID + '|')
   FROM #ParentChildHierarchy
  WHERE SuperTaskID = TaskID
  UNION ALL
  --=== Traverse the hierarchy and build the outputs as we go.
     -- This is the "recursive" part of this rCTE (Recursive CTE).
 SELECT  tbl.SuperTaskID
        ,tbl.TaskID
        ,TaskName       = SUBSTRING(tbl.TaskName,3,4000)
        ,tbl.TaskStart
        ,tbl.TaskEnd
        ,hLevel         = cte.hLevel + 1
        ,SortPath       = CONVERT(VARCHAR(8000),cte.SortPath + tbl.TaskID + '|')
   FROM cteTraverse           cte
   JOIN #ParentChildHierarchy tbl ON tbl.SuperTaskID = cte.TaskID 
  WHERE cte.SortPath NOT LIKE '%|' + tbl.TaskID + '|%' /*This breaks the "loops" built into the hierarchy*/
) --=== Produce the sorted, indented output
 SELECT DISTINCT /*The DISTINCT is necessary to get rid of the duplication*/
         SuperTaskID
        ,TaskID
        ,TaskName = CASE
                    WHEN hLevel = 0 THEN '+ ' + TaskName
                    ELSE REPLICATE('--',hLevel) + TaskName
                    END
        ,TaskStart
        ,TaskEnd
        ,hLevel     -- Comment out if you do not want to see this.
        ,SortPath   -- Comment out if you do not want to see this.
   FROM cteTraverse t
  ORDER BY t.SortPath
;


  PSB - Wednesday, January 25, 2017 8:45 AM

  Thanks but the code doesn't return any result set.

  Then you have something wrong with the data that you just tested with.  I used the test data that you posted and got the exact return you wanted.  Check the content of the test table you built.  I get the following from the code using the test data you posted in your original post.

  
SuperTaskID   TaskID        TaskName                             TaskStart               TaskEnd                 hLevel SortPath
------------- ------------- ------------------------------------ ----------------------- ----------------------- ------ -------------------------------------------
IEA6IKQDVRK5N IEA6IKQDVRK5N + DP Committee Gate Final Gate       2016-08-01 09:00:00.000 2018-04-05 17:00:00.000 0      |IEA6IKQDVRK5N|
IEA6IKQDVRK5N IEA6IKQDVRK55 --Handover Day                       2016-11-15 09:00:00.000 2016-11-15 17:00:00.000 1      |IEA6IKQDVRK5N|IEA6IKQDVRK55|
IEA6IKQDVRK5N IEA6IKQDVRK5P --Governance Committee               2017-01-05 09:00:00.000 2018-01-08 17:00:00.000 1      |IEA6IKQDVRK5N|IEA6IKQDVRK5P|
IEA6IKQDVRK5P IEA6IKQDVRK52 ----Agree major changes in scope etc 2016-11-04 09:00:00.000 2017-10-31 17:00:00.000 2      |IEA6IKQDVRK5N|IEA6IKQDVRK5P|IEA6IKQDVRK52|
IEA6IKQDVRK5P IEA6IKQDVRK5S ----Continue with team               2016-11-04 09:00:00.000 2017-10-31 17:00:00.000 2      |IEA6IKQDVRK5N|IEA6IKQDVRK5P|IEA6IKQDVRK5S|
IEA6IKQDVRK5P IEA6IKQDVRL3S ----Progress goals                   2016-11-04 09:00:00.000 2017-10-31 17:00:00.000 2      |IEA6IKQDVRK5N|IEA6IKQDVRK5P|IEA6IKQDVRL3S|
IEA6IKQDVRK6M IEA6IKQDVRK6M + DP Alpha release                   NULL                    2017-01-06 17:00:00.000 0      |IEA6IKQDVRK6M|

  (7 row(s) affected)

  
  

  --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)
  

• PSB

  SSChampion

  Points: 13195

  More actions

  January 25, 2017 at 10:18 am

  #1924914

  Jeff Moden - Wednesday, January 25, 2017 9:54 AM

  Jeff Moden - Wednesday, January 25, 2017 8:34 AM

  Here's the solution you asked for.  It also correctly places the "Handover Day".  The correct solution would be to turn the table into a true Adjacency List for ease of maintenance and update it for Nested Sets traversals for performance.  Please see the article at the following link for that.
  http://www.sqlservercentral.com/articles/Hierarchy/94040/
  
  
   WITH cteTraverse AS
( --=== Find the root nodes and start the "SortPath".
 SELECT  SuperTaskID
        ,TaskID
        ,TaskName   = SUBSTRING(TaskName,3,4000)
        ,TaskStart
        ,TaskEnd
        ,hLevel     = 0 --We will use this to determine "indentation"
        ,SortPath   = CONVERT(VARCHAR(8000),'|' + SuperTaskID + '|')
   FROM #ParentChildHierarchy
  WHERE SuperTaskID = TaskID
  UNION ALL
  --=== Traverse the hierarchy and build the outputs as we go.
     -- This is the "recursive" part of this rCTE (Recursive CTE).
 SELECT  tbl.SuperTaskID
        ,tbl.TaskID
        ,TaskName       = SUBSTRING(tbl.TaskName,3,4000)
        ,tbl.TaskStart
        ,tbl.TaskEnd
        ,hLevel         = cte.hLevel + 1
        ,SortPath       = CONVERT(VARCHAR(8000),cte.SortPath + tbl.TaskID + '|')
   FROM cteTraverse           cte
   JOIN #ParentChildHierarchy tbl ON tbl.SuperTaskID = cte.TaskID 
  WHERE cte.SortPath NOT LIKE '%|' + tbl.TaskID + '|%' /*This breaks the "loops" built into the hierarchy*/
) --=== Produce the sorted, indented output
 SELECT DISTINCT /*The DISTINCT is necessary to get rid of the duplication*/
         SuperTaskID
        ,TaskID
        ,TaskName = CASE
                    WHEN hLevel = 0 THEN '+ ' + TaskName
                    ELSE REPLICATE('--',hLevel) + TaskName
                    END
        ,TaskStart
        ,TaskEnd
        ,hLevel     -- Comment out if you do not want to see this.
        ,SortPath   -- Comment out if you do not want to see this.
   FROM cteTraverse t
  ORDER BY t.SortPath
;


  PSB - Wednesday, January 25, 2017 8:45 AM

  Thanks but the code doesn't return any result set.

  Then you have something wrong with the data that you just tested with.  I used the test data that you posted and got the exact return you wanted.  Check the content of the test table you built.  I get the following from the code using the test data you posted in your original post.

  
SuperTaskID   TaskID        TaskName                             TaskStart               TaskEnd                 hLevel SortPath
------------- ------------- ------------------------------------ ----------------------- ----------------------- ------ -------------------------------------------
IEA6IKQDVRK5N IEA6IKQDVRK5N + DP Committee Gate Final Gate       2016-08-01 09:00:00.000 2018-04-05 17:00:00.000 0      |IEA6IKQDVRK5N|
IEA6IKQDVRK5N IEA6IKQDVRK55 --Handover Day                       2016-11-15 09:00:00.000 2016-11-15 17:00:00.000 1      |IEA6IKQDVRK5N|IEA6IKQDVRK55|
IEA6IKQDVRK5N IEA6IKQDVRK5P --Governance Committee               2017-01-05 09:00:00.000 2018-01-08 17:00:00.000 1      |IEA6IKQDVRK5N|IEA6IKQDVRK5P|
IEA6IKQDVRK5P IEA6IKQDVRK52 ----Agree major changes in scope etc 2016-11-04 09:00:00.000 2017-10-31 17:00:00.000 2      |IEA6IKQDVRK5N|IEA6IKQDVRK5P|IEA6IKQDVRK52|
IEA6IKQDVRK5P IEA6IKQDVRK5S ----Continue with team               2016-11-04 09:00:00.000 2017-10-31 17:00:00.000 2      |IEA6IKQDVRK5N|IEA6IKQDVRK5P|IEA6IKQDVRK5S|
IEA6IKQDVRK5P IEA6IKQDVRL3S ----Progress goals                   2016-11-04 09:00:00.000 2017-10-31 17:00:00.000 2      |IEA6IKQDVRK5N|IEA6IKQDVRK5P|IEA6IKQDVRL3S|
IEA6IKQDVRK6M IEA6IKQDVRK6M + DP Alpha release                   NULL                    2017-01-06 17:00:00.000 0      |IEA6IKQDVRK6M|

  (7 row(s) affected)

  
  

  I see that you aren't using the ChildId or SubTaskID at all . But there is a slight modification .. The SuperTaskID s for the topmost are null . If I make that change your query does not return results .

  CREATE TABLE #ParentChildHierarchy

  (

  SuperTaskID NVARCHAR (20),

  TaskID NVARCHAR (20),

  SubTaskID NVARCHAR (20),

  TaskName NVARCHAR (4000),

  TaskStart DATETIME,

  TaskEnd DATETIME

  )

  INSERT INTO #ParentChildHierarchy (SuperTaskID , TaskID , SubTaskID , TaskName , TaskStart , TaskEnd )

  --ParentTasks

  SELECT NULL, 'IEA6IKQDVRK6M', NULL, '+ DP Alpha release', NULL, '2017-01-06 17:00:00.000'

  UNION

  SELECT NULL, 'IEA6IKQDVRK5N', NULL ,'+ DP Committee Gate Final Gate', '2016-08-01 09:00:00.000', '2018-04-05 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5N', 'IEA6IKQDVRK55', NULL, '--Handover Day', '2016-11-15 09:00:00.000', '2016-11-15 17:00:00.000'

  --SubTasks

  UNION

  SELECT 'IEA6IKQDVRK5N', 'IEA6IKQDVRK5P', 'IEA6IKQDVRK52' ,'--Governance Committee', '2017-01-05 09:00:00.000', '2018-01-08 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5N', 'IEA6IKQDVRK5P', 'IEA6IKQDVRK5S' ,'--Governance Committee', '2017-01-05 09:00:00.000', '2018-01-08 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5N', 'IEA6IKQDVRK5P', 'IEA6IKQDVRL3S' ,'--Governance Committee', '2017-01-05 09:00:00.000', '2018-01-08 17:00:00.000'

  ---Childs

  UNION

  SELECT 'IEA6IKQDVRK5P', 'IEA6IKQDVRK52', NULL ,'--Agree major changes in scope etc', '2016-11-04 09:00:00.000', '2017-10-31 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5P', 'IEA6IKQDVRK5S', NULL ,'--Continue with team', '2016-11-04 09:00:00.000', '2017-10-31 17:00:00.000'

  UNION

  SELECT 'IEA6IKQDVRK5P', 'IEA6IKQDVRL3S', NULL ,'--Progress goals', '2016-11-04 09:00:00.000', '2017-10-31 17:00:00.000'

  SELECT * FROM #ParentChildHierarchy

• PSB

  SSChampion

  Points: 13195

  More actions

  January 25, 2017 at 10:53 am

  #1924919

  Please help with the modifications to SuperTAskID null if parent . I am stuck . 🙁

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 25, 2017 at 11:32 am

  #1924928

  PSB - Wednesday, January 25, 2017 10:53 AM

  Please help with the modifications to SuperTAskID null if parent . I am stuck . 🙁

  Not sure what you are talking about.  Can you modify the results Jeff posted to show what you are expecting?

• PSB

  SSChampion

  Points: 13195

  More actions

  January 25, 2017 at 12:31 pm

  #1924950

  Lynn Pettis - Wednesday, January 25, 2017 11:32 AM

  PSB - Wednesday, January 25, 2017 10:53 AM

  Please help with the modifications to SuperTAskID null if parent . I am stuck . 🙁

  Not sure what you are talking about.  Can you modify the results Jeff posted to show what you are expecting?

  Basically the same result as Jeff posted but considering NULL SuperTAskID for + DP Alpha release                   and + DP Committee Gate Final Gate       as these are the parents

  SuperTaskID   TaskID        TaskName                             TaskStart               TaskEnd                 hLevel SortPath
  ------------- ------------- ------------------------------------ ----------------------- ----------------------- ------ -------------------------------------------
  NULL IEA6IKQDVRK5N + DP Committee Gate Final Gate       2016-08-01 09:00:00.000 2018-04-05 17:00:00.000 0      |IEA6IKQDVRK5N|
  IEA6IKQDVRK5N IEA6IKQDVRK55 --Handover Day                       2016-11-15 09:00:00.000 2016-11-15 17:00:00.000 1      |IEA6IKQDVRK5N|IEA6IKQDVRK55|
  IEA6IKQDVRK5N IEA6IKQDVRK5P --Governance Committee               2017-01-05 09:00:00.000 2018-01-08 17:00:00.000 1      |IEA6IKQDVRK5N|IEA6IKQDVRK5P|
  IEA6IKQDVRK5P IEA6IKQDVRK52 ----Agree major changes in scope etc 2016-11-04 09:00:00.000 2017-10-31 17:00:00.000 2      |IEA6IKQDVRK5N|IEA6IKQDVRK5P|IEA6IKQDVRK52|
  IEA6IKQDVRK5P IEA6IKQDVRK5S ----Continue with team               2016-11-04 09:00:00.000 2017-10-31 17:00:00.000 2      |IEA6IKQDVRK5N|IEA6IKQDVRK5P|IEA6IKQDVRK5S|
  IEA6IKQDVRK5P IEA6IKQDVRL3S ----Progress goals                   2016-11-04 09:00:00.000 2017-10-31 17:00:00.000 2      |IEA6IKQDVRK5N|IEA6IKQDVRK5P|IEA6IKQDVRL3S|
  NULL                     IEA6IKQDVRK6M + DP Alpha release                   NULL

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 25, 2017 at 2:33 pm

  #1924966

  PSB - Wednesday, January 25, 2017 12:31 PM

  Lynn Pettis - Wednesday, January 25, 2017 11:32 AM

  PSB - Wednesday, January 25, 2017 10:53 AM

  Please help with the modifications to SuperTAskID null if parent . I am stuck . 🙁

  Not sure what you are talking about.  Can you modify the results Jeff posted to show what you are expecting?

  Basically the same result as Jeff posted but considering NULL SuperTAskID for + DP Alpha release                   and + DP Committee Gate Final Gate       as these are the parents

  SuperTaskID   TaskID        TaskName                             TaskStart               TaskEnd                 hLevel SortPath
  ------------- ------------- ------------------------------------ ----------------------- ----------------------- ------ -------------------------------------------
  NULL IEA6IKQDVRK5N + DP Committee Gate Final Gate       2016-08-01 09:00:00.000 2018-04-05 17:00:00.000 0      |IEA6IKQDVRK5N|
  IEA6IKQDVRK5N IEA6IKQDVRK55 --Handover Day                       2016-11-15 09:00:00.000 2016-11-15 17:00:00.000 1      |IEA6IKQDVRK5N|IEA6IKQDVRK55|
  IEA6IKQDVRK5N IEA6IKQDVRK5P --Governance Committee               2017-01-05 09:00:00.000 2018-01-08 17:00:00.000 1      |IEA6IKQDVRK5N|IEA6IKQDVRK5P|
  IEA6IKQDVRK5P IEA6IKQDVRK52 ----Agree major changes in scope etc 2016-11-04 09:00:00.000 2017-10-31 17:00:00.000 2      |IEA6IKQDVRK5N|IEA6IKQDVRK5P|IEA6IKQDVRK52|
  IEA6IKQDVRK5P IEA6IKQDVRK5S ----Continue with team               2016-11-04 09:00:00.000 2017-10-31 17:00:00.000 2      |IEA6IKQDVRK5N|IEA6IKQDVRK5P|IEA6IKQDVRK5S|
  IEA6IKQDVRK5P IEA6IKQDVRL3S ----Progress goals                   2016-11-04 09:00:00.000 2017-10-31 17:00:00.000 2      |IEA6IKQDVRK5N|IEA6IKQDVRK5P|IEA6IKQDVRL3S|
  NULL                     IEA6IKQDVRK6M + DP Alpha release                   NULL

  So, to paraphrase, where SuperTaskID = TaskID SuperTaskID should display null instead of SuperTaskID.  That about sum it up?

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  January 25, 2017 at 4:16 pm

  #1924985

  PSB - Wednesday, January 25, 2017 10:53 AM

  Please help with the modifications to SuperTAskID null if parent . I am stuck . 🙁

  Is the test data and the results you want complete and accurate this time?

  --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)
  

• PSB

  SSChampion

  Points: 13195

  More actions

  January 25, 2017 at 4:54 pm

  #1924989

  Jeff Moden - Wednesday, January 25, 2017 4:16 PM

  PSB - Wednesday, January 25, 2017 10:53 AM

  Please help with the modifications to SuperTAskID null if parent . I am stuck . 🙁

  Is the test data and the results you want complete and accurate this time?

  yes . It's correct .