Tag Items between specific items

I have a table used for product assemblies that include a start row then build parts followed by a End row.

I need to tag the parts between the start and stop rows
i.e.
Part      Description      Tag
1234      Part 1      
5678      Part 2      
Start      Part Start      
Build 1      Part 3      X
Build 2      Part 4      X
Build 3      Part 5      X
End      Part End      
8938      Part 6      

What is the best way to do this?

Thanks
jdr0606Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jdr0606Connect With a Mentor Author Commented:
I actually got one of my developers to write me a query that accomplished what I needed.

I have attached what was created for me
TmpTag.txt
0
 
David ToddSenior DBACommented:
Hi,

SQL doesn't at this stage have a concept of next-row/previous-row. Conceptually each table is an unordered set.

Now if you are talking about sub assemblies, then a hierarchy could be helpful:
Part	Description	Belongs to	Quantity
a	Car		
b   	Wheel		a		5
c	Tyre		b		1
d	Hub		b		1
e	Tyre Valve	b		1
f	Chasis		a		1
g	Front Seats	a		2
h	Back Seat	a		1

Open in new window


Is that the sort of thing you are trying to do?

Regards
  David
0
 
jdr0606Author Commented:
Not exactly

We have products that require assembly and in a certain order so in the product table we have a product row (start) followed by the individual parts that make up the assembly and then a Product row (stop) indicating the end of the build product.

What I'm trying to do is simply tag the products that fall between start and stop rows.

As in my example the parts Build 1, Build 2 and Build 3 are tagged because they occur after a Start and before Stop rows.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
David ToddSenior DBACommented:
Hi,

What forms the order? While we logically think of previous row, this row, next row, SQL doesn't. So what are you ordering on?

Regards
  David
0
 
jdr0606Author Commented:
Sorry I forgot a key component.  The assembled parts are ordered by order #

Order #            Part            Description      Tag
Order 1            1234            Part 1      
Order 2            5678            Part 2      
Order 3            Start            Part Start      
Order 3            Build 1            Part 3            X
Order 3            Build 2            Part 4            X
Order 3            Build 3            Part 5            X
Order 3            End            Part End      
Order 4            8938            Part 6
0
 
David ToddSenior DBACommented:
Hi

How many Part Start|Part End pairs in there?

Regards
  David
0
 
jdr0606Author Commented:
In the table there are thousands of Part Start and Part Ends but they will always be
Part Start
Part
part
part
Part End

The Part Start and Part End are always the same but the parts betwen can be different

i.e.
Part Start
Part A
Part B
Part End
Part Start
Part C
Part D
Part End
0
 
David ToddSenior DBACommented:
Hi,

Looking at the sample you provided
Order #            Part            Description      Tag
Order 3            Start            Part Start      
Order 3            Build 1            Part 3            X
Order 3            Build 2            Part 4            X
Order 3            Build 3            Part 5            X
Order 3            End            Part End      

What details the sequence within Order 3? There has to be something. If there is nothing then it needs to be created first.

Is this tag a one-off, or a repeated process? What do you do with the parts once tagged?

I can see how to find the part end for one part start, but not how to do so for a set without creating a huge Cartesian join.

Regards
  David
0
 
David ToddSenior DBACommented:
Hi,

I noted that your developer had some info on how to get the table ordered that you didn't give us here.

I was trying to think of how it could be done without using a cursor. Obviously a good solution is to use a cursor.

Regards
  David
0
 
jdr0606Author Commented:
I had one of my developers create a solution
0
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.

All Courses

From novice to tech pro — start learning today.