Looking for combination - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Looking for combination

Author

Topic

TestEngineer
Starting Member

29 Posts

Posted - 2006-01-31 : 13:20:19

Below is a list of related tables for tracking serial number styles.

PartNumbers:
PN_ID int IDENTITY (PK)
PN_Val varchar(50)

Serial_Style_Combos:
Style_Combo_ID int IDENTITY (PK)
Description varchar(50)

Serial_Style_Assignments:
SSA_ID int IDENTITY (PK)
Style_Combo_ID int (FK)
PN_ID int (FK)

Serial_Style_Groupings
SSG_ID int IDENTITY (PK)
Style_Combo_ID int (FK)
Serial_Style_ID int (FK)

Serial_Number_Styles:
SNS_ID int IDENTITY (PK)
Serial_Style_Description varchar(50)

Example data:
Serial_Number_Styles
SNS_ID Serial_Style_Description
1 'Standard Style'
2 'Another Style'
3 'Yet Another Style'

Serial_Style_Groupings:
SSG_ID Style_Combo_ID Serial_Style_ID
1 1 1
2 1 2
3 2 1
4 2 2
5 2 3

Serial_Style_Assignments:
SSA_ID Style_Combo_ID PN_ID
1 1 1
1 1 2
1 2 3

Serial_Style_Combos:
Style_Combo_ID Description
1 'Customer1_Combo'
2 'Customer2_Combo'

PartNumbers:
PN_ID PN_Val
1 '1000 1970'
2 '1000 1971'
3 '1000 1384'

What this gives me is that 1000 1970 and 1000 1971 both share the same serial number styles. If I make a change to the Serial_Style_Groupings table, I can change the serial styles used by a grouping of products sharing the same valid serial style IDs.

What I'd like to do is identify a serial_style_combo based on the serial_Style_Groupings. In other words, if I were to pass to a stored procedure the values 1 and 2 (for the serial style IDs), it would return a table of all the part numbers using these two and ONLY these two style IDs. It would return '1000 1970' and '1000 1971'. It would not return '1000 1384' then. If I send the procedure 1,2, and 3, it would return 1000 1383 only. I reduced the amount of data in the table to shorten the post somewhat, but I'm currently working with about 22 different serial styles and hundreds of different part numbers.

Any advice would be appreciated.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-01 : 09:44:16

quote:
It would return '1000 1970' and '1000 1971'. It would not return '1000 1384' then. If I send the procedure 1,2, and 3, it would return 1000 1383 only

What is the logic you use?
Can you explain it?

Madhivanan

Failing to plan is Planning to fail

TestEngineer
Starting Member

29 Posts

Posted - 2006-02-01 : 11:01:53

The serial styles assigned to a part number represent different identification methods along the process. There are some part numbers that have boards as sub-assemblies located within containers that are given an intermediate serial number then later given a shipping serial number value. In some cases, multiple serials are programmed into an IC on the board.

The serial style groupings table defines what serial styles are applied to a particular serial style combination identified in the serial style combos table. This way, a part number can be assigned to multiple serial styles. If another part number will also use those same serial styles, it would get the same serial style combo ID assigned to it in the serial style assignments table.

If a change is made to a particular combination of serial styles, it would then be reflected in all the part numbers using that combination, so we won't have to worry about a change being made and a part number retaining old formatting.

What I'd like to do:
Pass a particular combination of serial styles. The stored procedure would then determine if it belongs to a particular existing combination. If it does, it would return the serial style combo record ID. If it does not, it would create a new serial style combo record and build the definition for the combination in the serial style groupings table.

Another stored procedure could then call this stored procedure to determine all the different part numbers using the particular combination passed. It would return a table of part numbers that are using the combination.

I've accomplished some of this in my client side code, but I'd like to get it on the SQL server end. The way I'm doing this in the client side is to build a pattern based on the serial_number_styles table. I create a pattern of 1's and 0's for each serial_style_combo. The MSB of the pattern represents if the first serial number style in the serial number styles table is used by the current combination I'm iterating through. The second MSB is based on the second serial style, etc. I then build a pattern similarly based on my test serial style combination. I check if the patterns match, and if they do, I know that the current combination I'm on is an exact match for the serial style combination I've specified. In other words, I've identified which serial style combo ID I'm looking at. From this, I can retrieve the part numbers using that combo ID.

For stored procedures, I found a function online that I can pass a space-seperated list of numbers to and it will return a table that has one value in each record. I'm using this to insert new serial style groupings for a new serial style combo.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources