Parent child hierarchy

Question

Parent child hierarchy

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

PSB SSChampion Points: 13195 More actions · Answer 1

PSB - Wednesday, January 25, 2017 4:54 PM
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 .

Any help will be appreciated . 🙂

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

PSB - Thursday, January 26, 2017 10:09 AM
PSB - Wednesday, January 25, 2017 4:54 PM
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 .
Any help will be appreciated . 🙂

You do realize that we are volunteers giving of our own time to help. Sometimes our paying job or our personal lives do take a priority to helping people on this site. If this a critical task and you are relying on the internet to get it done, you may want to rethink this. If you can be patient and allow us to help when we can, then we are more than willing to help. At the moment, I am just on a short break at work and seeing what is going on.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

PSB - Wednesday, January 25, 2017 4:54 PM
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 .

I'm thinking that you're in deep kimchee because you're the one that has to maintain it over time. With the idea of "teaching a man to fish"...

Look at the code I wrote. Now, look at the change you wrote above. What's another name for the top level "parent" in a hierarchy? The answer is "Root node". Now, look at the code I wrote. Find the code having to do with finding the root nodes. Run just that code (single SELECT) and you'll find that, with the corrected data you posted, it returns nothing. That correctly identifies the problem

Again, look at what you wrote above...

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

Now, ask your self what you could do to the section of the code that finds the root nodes to actually find the root nodes for your modification. That's the only thing that needs to be changed in the code I wrote. Give it a whirl on your own. You can actually do this.

--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 · Answer 4

Hi,
I have made changes to the code . One issue that I notice is that the child is not sticking to the parent . + DP Alpha release is a SuperTask without any tasks underneath according to the data but the result shows
--Handover Day
--Governance Committee
----Agree major changes in scope etc
----Continue with team
----Progress goals

as children of + DP Alpha release .

; WITH cteTraverse AS

( --=== Find the root nodes and start the "SortPath".

SELECT SuperTaskID

--,CONVERT(VARCHAR(8000),'|' + 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 IS NULL

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

,tbl.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.hLevel,TaskID

Thanks,
PSB

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

That's because you changed the ORDER BY from "SortPath" to "t.hLevel,TaskID". Change the ORDER BY back to "SortPath"

You also commented out the formula I had for TaskName to be presented correctly. You only needed to change the WHERE clause in the first SELECT of the rCTE to find the NULL SuperTaskIds. Why did you make all the other changes?

Heh... stop breakin' stuff. The code will work better. 😉

--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 · Answer 6

Jeff Moden - Sunday, January 29, 2017 8:29 AM
That's because you changed the ORDER BY from "SortPath" to "t.hLevel,TaskID". Change the ORDER BY back to "SortPath"
You also commented out the formula I had for TaskName to be presented correctly. You only needed to change the WHERE clause in the first SELECT of the rCTE to find the NULL SuperTaskIds. Why did you make all the other changes?
Heh... stop breakin' stuff. The code will work better. 😉

SUBSTRING(TaskName,3,4000)

This is stripping letters for some of the tasknames .. example --mplete Comp Pillars instead of --Complete Comp Pillars in the real data

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

PSB - Sunday, January 29, 2017 12:40 PM
Jeff Moden - Sunday, January 29, 2017 8:29 AM
That's because you changed the ORDER BY from "SortPath" to "t.hLevel,TaskID". Change the ORDER BY back to "SortPath"
You also commented out the formula I had for TaskName to be presented correctly. You only needed to change the WHERE clause in the first SELECT of the rCTE to find the NULL SuperTaskIds. Why did you make all the other changes?
Heh... stop breakin' stuff. The code will work better. 😉
SUBSTRING(TaskName,3,4000)
This is stripping letters for some of the tasknames .. example --mplete Comp Pillars instead of --Complete Comp Pillars in the real data

Not according to the test data you posted. 😉

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