Create a Leveling of Intermediaries

Question

Post reply

Create a Leveling of Intermediaries

manoj.ramaiah

SSC Eights!

Points: 864
More actions
May 30, 2013 at 11:07 pm

#275827

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.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

The explanation provided is not very clear and this is the reason why nobody has given an answer yet

It would be helpful if you can provide some sample data and the expected results based on that sample data

Additionally, it would help people who would like to do some testing before they post answers

Note: Please don't create more than one threads for the same issue. It just fragments replies and wastes people time.

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/

manoj.ramaiah SSC Eights! Points: 864 More actions · Answer 2

Hi Table is:

CREATE TABLE Intermediary

(

IntermediaryPK INT ,

IntermediaryID NVARCHAR(20),

IntermediaryName NVARCHAR(200),

IntermediaryTypeID NVARCHAR(1),,

ParentIntermediaryID NVARCHAR(20),

IntermediaryTypePK TINYINT,

ParentIntermediaryPK INT

GrandParentIntermediaryPK INT

GrandParentIntermediaryID NVARCHAR(20)

)

== Insert the test data into the test table

INSERT INTO Intermediary

(IntermediaryPK, IntermediaryID, IntermediaryName, IntermediaryTypeID, ParentIntermediaryID, IntermediaryTypePK, ParentIntermediaryPK, GrandParentIntermediaryPK, GrandParentIntermediaryID)

SELECT '552',200244584261,'A''1''201841943403''1' '6459''6459''201841943403'UNION ALL

SELECT '553','200983879092','B''1''200707895681''1' '6462''6459''200707895681'UNION ALL

SELECT '554','200925413387','C''1''200707895681''1' '6462''6462''200707895681'UNION ALL

SELECT '555','200472620781','D''1''200707895681''1''6462' '6462''200707895681'UNION ALL

SELECT '556','201902784325','E''1''200707895681''1''6462' '6462''200707895681'UNION ALL

SELECT '557','201874832909','F''1''200707895681''1''566' '6462''200707895681'UNION ALL

SELECT '558','201264024229','G''1''200707895681''1' '566''6462''200707895681'UNION ALL

SELECT '559','201725870455','H''1''201062751762''1''566''6462''200707895681'

Actualy LevelID is new column and I have to update This column using this

algorithem. Remaining all column are same like with its previous task. So I need to write query as algo stated...

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

Can you also post the expected results based on this 8 sample rows?

What is the Level you are expecting for each of these 8 rows.

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/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

manoj.ramaiah (5/30/2013)
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.

Are you saying that you just want to change the basic table structure from what it is to a proper Adjacency List (Parent/Child List)?

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

manoj.ramaiah SSC Eights! Points: 864 More actions · Answer 5

Thanks for your reply

Actually I want to write 3 seperate queries.

For example: For leval 1, I want those rows who has grand children.

For level 2, I want those rows who has children.

For level 3, I want those rows who dont have any children.

These queries are based on IntermediaryID, ParentIntermediaryID, GrandParentIntermediaryID.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

There might be some confusion of terms here. Are you saying that if a give IntermediaryID (I'm assuming that's the top level of the hierarchy here) has no children, you want it to show up as a "Level 3"?

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