Posted by Thomas Bihn on 12/16/05 16:46

I have a similar issue as the good doctor. Mine is with serial numbers.
I work at a manufacturing facility that makes printed circuit board
assemblies for a wide range of applications and companies (from vacuum
cleaners to automotive to medical). Some of these devices have more than
one unique serial number. One recently has more than one serial number
type with three unique serial number of one type, 1 each of 4 other
types. This makes 5 types of serial numbers and 7 unique serial numbers.

I have a Serials_Number_Generated table that has the following
fields/elements/columns/(whatever you call them):
Serial_Number_ID bigint PK Identity
Serial_Number_Value nvarchar 100
Serial_Number_Index int
Serial_Number_Style_ID bigint
Serial_Transaction_ID bigint

When serial numbers are created and or stored in the database(whatever
you call it), I send the information to a stored procedure to insert
into a table/entity/collection of information about an item or
event/(whatever you call them). Anyway, that I add a transaction to a
Serial_Number_Transactions table, identifying the Operator_ID,
Transaction_DateTime, Facility_ID. I use the Transaction_ID (the PK and
Identity field for the transactions table) in the serial numbers
generated table. This is a one-to-many relationship to ensure that no
invalid transaction IDs are entered.

I have implemented this structure already and if it a bad structure, any
suggestions will be appreciated.

Anyway, the problem I have is this one board with 7 unique serial
numbers on it. The customer would like to have a serial number report
identifying the serial numbers programmed into each board.

I have another table, Serial_Numbers_Utilized to track the serial
numbers that are actually utilized and assign them to a unique Board_ID
(The afformentioned printed circuit board assembly). The
schema/structure/layout(whatever you call it) of this table is:
Serial_Number_Utilized bigint PK (identity)
Serial_Number_ID bigint
Board_ID bigint
Serial_Transaction_ID bigint

When my programming/MDA(Manufacturing Defect Analyzer) station goes to
program the board, it retrieves the unutilized, generated serial numbers
from the database that is referenced in yet another table and adds a new
record to the Serial_Numbers_Utilized table with a link to a new
transaction ID for this action as well as the unique board id the
numbers are being assigned to and the serial id for the serial value
assigned to the board. So for each board that makes it through the
programmer station, there will be 7 new records in the
Serial_Numbers_Utilized table/entity/(whatever you call it).

The customer would like a report for each board identifying the serial
numbers that were utilized to program this board. I started creating a
view/query/select/(whatever you call it) that I could import into Excel
for a report to the customer that had one board per row in the Excel
worksheet.

I've reasoned that I need a few nested Select queries focused for this
particular product (instead of a more general one for all products). I
first created a select statement to get a list of just the boards that
passed all stations (I won't go into the extra detail about the Boards,
Board_Transactions, Boards_Tested,Test_Records, etc tables here). I
filtered for the boards that are of this unique board part number. I
also sorted by board Id. This select statement consists of Inner Joins
of several tables but returns:
Board_ID
Serial_Number_Value
Serial_Number_Style_ID
Serial_Transaction_ID

Each serial will have a uniqe serial transaction id, which is in the
order the serials were added, which is also important for the three
repeating serials.
What I get is
Board_ID, Serial_Number_Value, Serial_Number_Style_ID,
Serial_Transaction_ID
21,A (I'll conceal the actual SN for security),1,70157
21,B,2,70162
21,C,3,70163
21,D,4,70159
21,E,4,70160
21,F,4,70161
21,G,5,70158
43,H,1,70278
43,I,2,70283
etc...
The report would look something like this:
Board_ID,Type1,Type2,Type3,Type4 0, Type4 1, Type 4 2, Type 5
21,A,B,C,D,E,F,G
43,H,I
Of course there are actual serial values inplace of the letters. I was
going to create select statements that used the above result set/record
set/table/data(whatever you call it) as the source and then create a
master select statement that joined all the tables on board id, but I
run into a problem with the Type4, where there are three records per
board, order is important here also.

I found a reference to cross-tabs/pivots/(whatever you call them) that
shows how to use a T-SQL CASE statement in an aggregate function but I
cannot consolidate the numbers in any way here and especially don't want
to risk having SQL convert a trinary number into a bigint and adding it
to another.

I also found a reference to creating a csv record inside the field for
that board record. In this case, you'd have three valuees seperated by 3
columns for each field returned. I may have to go with this method, but
it only seemed to work in Query Analyzer. When attempting to do it in a
view, it timed out. This uses a UDF I found on another site.

Has anyone found a way to do this or will I just need to create a
seperate table identifying the serial_id and ordinal position (1,2,3)
that I'll join for that query. I would have to somehow go back to all
the serial numbers generated with that type joined with the ID's
utilized and this new table and enter 1,2,3,1,2,3,1,2,3 manually or with
another piece of software. I'm thinking this is my only hope at this
point.

Sorry for the long post.

*** Sent via Developersdex http://www.developersdex.com ***

[Back to original message]