Link to home
Start Free TrialLog in
Avatar of maverick0728
maverick0728Flag for United States of America

asked on

T-SQL CTE for finding Parent, Prior Peer, Next Peer, Child

Attached is a spreadsheet.  I have this data in a table "asm"  I need to produce the columns:
col E - Parent
col F - Prior Peer
col G - NextPeer
col H - Child
The numbers in the columns E, F, G, H are the "AssemblySeq" numbers.  How would I write a sql server 2012 CTE procedure to derive these values in col E-H?  

Please provide complete SQL statement.
example-parent-child.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of maverick0728

ASKER

I actually need to generate the data in columns E-H, not just select it.  Please, how would I generate the data in these columns.
Data in E-H is already generated. How is it generated? How have you generated them.

What is data source? What is the algorithm to create them?
I think the example I sent you was not very good.  Attached is a better example.  column I shows what I am trying to get with an explanation on some of them.  I hope this helps.  I need some sort of subquery maybe with a CTE?
ExampleParentChild-2.xlsx
Unless someone else post a solution, I will be back on this question this evening (now it is about 7:30 AM).
thank you sorry for the initial confusion
example of what I need:

1. col E - Parent  <--Parent  depends on two columns: itemno, assemblyseq as described under note A below..
2. col F - PriorPeer            <-- PriorPeer depends on columns: ...  
3. col G - NextPeer           <-- NextPeer depends on columns: ...  
4. col H - Child                  <-- Child depends on columns: ...  

note A
itemno      assemblyseq
1.1.1.1             4                               you say Parent is 3  why?
1.1.1.2             5                               you say Parent is 3  why?
complete the rest

Your information so far is incomplete and super confusing.

This post has been revised...
yes it is super confusing.  I have to clean up data and put it into this format so we can import it into another system that wants data in this format.  In reference to:
note A
itemno      assemblyseq
1.1.1.1             4                               you say Parent is 3  why?
1.1.1.2             5                               you say Parent is 3  why?

parent is 3 for 1.1.1.1 and 1.1.1.2 because the prefix for these is 1.1.1 and itemno 1.1.1 says assemblyseq  = 0

The rules are:
if itemno = 0.0 then parent = 0
if itemno ends with .0 then parent = 0
otherwise, look at the prefix of the itemno and use the prefix to find the matching itemno. For example:
itemno = 1.1.1, prefix is 1.1, we need sql to find the itemno 1.1, and look up its assemblyseq.  the assemblyseq will be what we set "parent" = assemblyseq

example 1:
itemno = 1.1.1, prefix is 1.1, we find that for itemmo 1.1, the assemblyseq = 2, so for itemno 1.1.1, set parent = 2

example 2:
itemno = 1.1.1.1, prefix is 1.1.1, we find that for itemmo 1.1.1, the assemblyseq = 3, so for itemno 1.1.1, set parent = 3

example 3:
itemno = 1.1.2, prefix is 1.1.1, we find that for itemmo 1.1.1, the assemblyseq = 3, so for itemno 1.1.2, set parent = 3

example 4:
itemno = 1.1.2, prefix is 1.1, we find that for itemmo 1.1, the assemblyseq = 2, so for itemno 1.1.2, set parent = 2

example 5:
itemno = 2.7.1, prefix is 2.7, we find that for itemmo 2.7, the assemblyseq = 40, so for itemno 2.7.1, set parent = 40
The article was helpful and I was able to put some queries together that worked.