?
Solved

Tag Items between specific items

Posted on 2014-01-16
10
Medium Priority
?
372 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
  • 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 

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

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.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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