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

maverick0728
maverick0728 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database and Application Developer
Commented:
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

Author

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.
Mike EghtebasDatabase and Application Developer

Commented:
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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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
Mike EghtebasDatabase and Application Developer

Commented:
Unless someone else post a solution, I will be back on this question this evening (now it is about 7:30 AM).

Author

Commented:
thank you sorry for the initial confusion
Mike EghtebasDatabase and Application Developer

Commented:
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...

Author

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

Author

Commented:
The article was helpful and I was able to put some queries together that worked.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial