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
maverick0728Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
First doing it without CTE:
Select 
  Parent	
  , PriorPeer	
  , NextPeer	
  , Child
From asm

Open in new window

Note: In the excel file you have PriorPeer without space. If you need to have space between, then use PriorPeer  As [Prior Peer].

And here is using CTE:
;With CTE
As
(
Select 
  Parent	
  , PriorPeer	
  , NextPeer	
  , Child
From asm
)
Select * From D

Open in new window


Take a look at: Subqueries at a glance #4
Subqueries at a glance
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
maverick0728Author Commented:
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.
0
Mike EghtebasDatabase and Application DeveloperCommented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

maverick0728Author Commented:
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
0
Mike EghtebasDatabase and Application DeveloperCommented:
Unless someone else post a solution, I will be back on this question this evening (now it is about 7:30 AM).
0
maverick0728Author Commented:
thank you sorry for the initial confusion
0
Mike EghtebasDatabase and Application DeveloperCommented:
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...
0
maverick0728Author Commented:
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
0
maverick0728Author Commented:
The article was helpful and I was able to put some queries together that worked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.