Generate a Unique Identifier (SKUCode) for a product in SQL

Roger Binny 21 Reputation points
2022-11-16T01:20:59.253+00:00

Hi,

I am working with products data and I am required to generate a unique identifier number (AKA SKUCode) for each unique product across a brand level. This is a bit complex requirement and I do not know how well I can explain, but here you go!

We work with a few brands, each brand will have some locations where they do their business. Each location sells a few products. Naturally, 90-95% of the products of a brand overlap within their locations.

Once in every quarter we review the products and identify if there are any new products got added to each location. If any new products gets added across that brand (meaning the product is not available ever in any of the locations) we will assign a brand new identifier (derived by using it's brand ID).

For this question let's say there is a Brand with the name "A" and it got 3 locations "P" ,"Q" and "R".

Following is the simpler version of the Output Table.

IF OBJECT_ID(N'tempdb..#Products') IS NOT NULL  
BEGIN  
DROP TABLE #Products  
END  
  
GO  
CREATE TABLE #Products  
(ProductName VARCHAR(50),  
LocationName VARCHAR(50),  
BrandName VARCHAR(50),  
Price DECIMAL(10,2),  
ProductSize VARCHAR(10),  
ProductSKU INT,  
)  
  
INSERT INTO #Products  
SELECT 'W','P','A',1.09,'Small',50001  
UNION ALL  
SELECT 'W','Q','A',1.29,'Small',50001  
UNION ALL  
SELECT 'W','R','A',1.49,'Small',50001  
UNION ALL  
SELECT 'V','P','A',1.99,'Small',50002  
UNION ALL  
SELECT 'U','Q','A',2.99,'Small',50003  
UNION ALL  
SELECT 'M','R','A',3.49,'Small',50004  
UNION ALL  
SELECT 'M','R','A',3.99,'Medium',50005  
UNION ALL  
SELECT 'M','R','A',4.29,'Large',50006  
UNION ALL  
SELECT 'N','P','A',5.99,Null,50007  
UNION ALL  
SELECT 'N','R','A',7.99,Null,50008  
UNION ALL  
SELECT 'G','P','A',2.49,Null,50009  
UNION ALL  
SELECT 'G','P','A',12.49,Null,50010  
UNION ALL  
SELECT 'Y','P','A',13.49,Null,50011  
UNION ALL  
SELECT 'Z','Q','A',9.99,'Extra',50012  

Select * From #Products

260911-image.png

If we get a new Product "X" for locations "Q" and "P", because we have never seen this product before at any location for the same brand, we assign a new SKUCode to it. The current maximum SKUCode in the final table is 50012 so the new SKUCode for Product X will be 50013

Here's the simple version of Source table.

IF OBJECT_ID(N'tempdb..#Source') IS NOT NULL  
BEGIN  
DROP TABLE #Source  
END  
  
  
CREATE TABLE #Source  
(ProductName VARCHAR(50),  
LocationName VARCHAR(50),  
BrandName VARCHAR(50),  
Price DECIMAL(10,2),  
ProductSize VARCHAR(10)  
)  
  
INSERT INTO #Source  
SELECT 'X','P','A',11.99,'B'  
UNION ALL  
SELECT 'X','Q','A',13.99,'C'  
UNION ALL  
SELECT 'Y','Q','A',10.35,'Ergo'  
UNION ALL  
SELECT 'Z','R','A',15.99,'Extra'  
UNION ALL  
SELECT 'Z','Q','A',25.99,'Super'  
UNION ALL  
SELECT 'Z','R','A',33.33,'Kah'  
  

Basically if a product is completely new and was never seen in any locations of the brand, then we need to find the maximum identifier number of the brand and increment it by 1.

This is how the output should look like for Product X

260826-image.png

The whole final table should look like this.
260815-image.png

Until here, the requirement looks okay...but things get more complicated with the following scenario.

As you see Location P was already selling a product named Y. If Product Y is introduced to location Q, it shouldn't get a new product identifier but get the unique identifier of location P, Because it's the same product. I see the size is different but that isn't part of the criteria to assign the SKU Codes. We just need to see the price bands which are not available by default.

I need to create a price brand (basically a Row number or Dense Rank partition by brand,location,item order by price).

I'll explain price band a little more after a few lines.

So when Product "Y" got added to Location "Q", it gets the same SKU because the product is already available at the same price band.

The output should look like this

260816-image.png

The whole table will look like this

260848-image.png

Now another case here.

As wee see, Product "Z" is already sold at Location "Q" and it's SKU Code is 50012.

as per the source data, Product Z's second price band got added to Location Q and at the same time product Z got introduced in location "R".

So the Product Z's second price band should get a new SKUCode, 50014 (because 1 more of the current maximum SKUCode, 50013, is 50014) and location R will get the same SKUCodes of location Q as it is has the same item name and 2 price bands.

So the output will look like this.

260859-image.png

The Final output table should look like this

260817-image.png

Could you please help me how would I write a stored procedure or SQL to achieve the requirement? It doesn't need to be a single SQL statement, it could use derived talbes or temp tables or anything. My SQL Server version is 2016.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,264 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,596 questions
{count} votes