maverick0728
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
What is data source? What is the algorithm to create them?
ASKER
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
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).
ASKER
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...
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...
ASKER
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
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
ASKER
The article was helpful and I was able to put some queries together that worked.
ASKER