Create a Leveling of Intermediaries

• manoj.ramaiah

  SSC Eights!

  Points: 864

  More actions

  May 30, 2013 at 11:07 pm

  #274382

  Hi,

  I have a table called Intermediary, which have some columns like

  CREATE TABLE Intermediary

  (

  IntermediaryPK INT ,

  IntermediaryID NVARCHAR(20),

  IntermediaryName NVARCHAR(200),

  IntermediaryTypeID NVARCHAR(1),

  ParentIntermediaryID NVARCHAR(20),

  IntermediaryTypePK TINYINT,

  LevelID TINYINT,

  ParentIntermediaryPK INT,

  GrandParentIntermediaryPK INT,

  GrandParentIntermediaryID NVARCHAR(20)

  )

  The basic idea is to use only the ParentIntermediaryID column to create a leveling of intermediaries. The algorithm is that if someone does not have any children, he will be level 3, etc.

  So the algorithem is:

  1.Divide the intermediaries into levels. 1 = grand parent (has grand children). 2 = parent (has children). 3 = leaf (no children).

  2.For each level 3 intermediary:

  a.Set level 2 parent by the parent ID given

  b.Set level 1 parent by the parent’s parent ID given

  3.For each level 2 intermediary:

  a.Set level 2 parent as itself

  b.Set level 1 parent by the parent ID given

  4.For each level 1 intermediary, set level 1 parent as itself

  So I have to write a script (SQL) to accomplish this algorithem. Please help me out.

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  May 31, 2013 at 1:27 am

  #1619970

  Duplicate post. Please direct all your replies here.

  http://www.sqlservercentral.com/Forums/Topic1458521-391-1.aspx

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/