Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations Expand / Collapse
Author
Message
Posted Thursday, April 3, 2014 8:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
kathy.m.davis.ctr (4/3/2014)
Thank you for the excellent information! Is there a way to order the personnel in each level by something besides EmployeeID? My user wants to specify the order of the personnel in each level. For example, in level 2, instead of ordering Lynne, Bob, Ken and Marge, he wants Marge, Bob, Lynne, Ken. He wants to do this for each level. Our table has a column UICOrder (int) where the user enters the order of how he wants the items in each level to appear. Your solution in this article works for me except for the ordering of the levels. Any assistance is greatly appreciated!

Thank you,

Kathy Davis


I'll have to take a look at this after work tonight, Kathy. And, thanks for the feedback.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1558012
Posted Thursday, April 3, 2014 1:19 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 11:29 AM
Points: 4, Visits: 96
Jeff,

Thank you soooo much! I have been working on this all week and have not been successful.

Kathy
Post #1558209
Posted Monday, April 7, 2014 8:13 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 11:29 AM
Points: 4, Visits: 96
Hi Jeff! Have you had time to look at my problem?

Thanks,

Kathy Davis
Post #1559094
Posted Monday, April 7, 2014 5:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
Apologies. I really had to think about this.

First, I guess I don't understand why you would need an order in the output. The output bears no resemblence to a graphical org chart. It's all vertical data that most users won't ever see in that form.

The second thing is that if your REALLY need it in a given vertical order, then you can't use EmployeeIDs either as the child or parent ID. Instead, you'd need to predetermine what the order would be according to the graphical org chart and assign a position number to each node. The child and parent IDs would then be based on those positions and you would simply add the information for the employee holding that current position. The good part about that is that it becomes stupid simple to change someone in a position. The bad part is, you have to know what the org chart should look like so that you can assign the positions and the parents of each position.

That's about the only way I can think of doing this without it being by EmployeeID or by Name.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1559311
Posted Tuesday, April 8, 2014 1:48 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 11:29 AM
Points: 4, Visits: 96
Jeff,

Thank you so much for your assistance. I will use your advice!

Kathy
Post #1559688
Posted Tuesday, April 8, 2014 10:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
Thanks for the feedback, Kathy. If you get a chance, stop back and let us know how it worked out. I think originally deciding how to create the positional ID notation might be a bit of a chore but then maintenance becomes easy after that.

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1559769
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse