Solved

Tag Items between specific items

Posted on 2014-01-16
10
354 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
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.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

856 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