Solved

Tag Items between specific items

Posted on 2014-01-16
10
356 Views
Last Modified: 2014-01-25
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
0
Comment
Question by:jdr0606
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39787598
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
 

Author Comment

by:jdr0606
ID: 39787621
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39787645
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:jdr0606
ID: 39787687
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39787710
Hi

How many Part Start|Part End pairs in there?

Regards
  David
0
 

Author Comment

by:jdr0606
ID: 39787721
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39789546
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
 

Accepted Solution

by:
jdr0606 earned 0 total points
ID: 39795204
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39795226
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
 

Author Closing Comment

by:jdr0606
ID: 39808447
I had one of my developers create a solution
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question