April 4, 2017 at 10:05 am
Working with SKU's (Bar Codes) I need to find SQL code that will update a Price column based on a partial comparison. The Table is called 'Inventory' and the field for the SKU is 'ItemNum' there is a Price column that holds the price of the Item/SKU.
I need this to work for Sku's that are 12 in length, as these may include a leading zero and the trailing check digit...but OUR DB and scanner omits the leading zero and the trailing check digit.
So if I have a SKU that is:
039513757634 [LEN = 12] and has a Price of $10.00, I need that Price to update if I am comparing it to 39513757634 [LEN = 11] (no leading zero) or 3951375763 [LEN = 10] no leading zero or trailing check digit.
THe SKU could also have no leading zero: 10700702166 but then a check digit at the end.
There could be over 60,000 SKUs so I need the code to by dynamic in that regard.
I have no clue where to even start with this, any nudge is appreacted.
The premise here is, when we pull data from an old SAMs4 cash register, that may be programmed to recognize a leading zero and the trailing check digit, to update the Price of that item into our master DB that will have only the middle 10 digits of that SKU...
Thanks,
Chris
April 4, 2017 at 10:15 am
With no sample data, this is a little difficult. At a guess, maybe something like...DECLARE @Newprice decimal(12,2), @SKU varchar(10);
--Assume you provide the SKU so that it has it's leading zero's and no check digit
SET @SKU = '00000012345'; --11 characters
SET @Newprice = 100.99;
UPDATE Mytable
SET Price = @Newprice
WHERE RIGHT('0000000000' + CAST(SKU AS varchar(11)),11) = @SKU --CAST incase your SKU field is a INT/BIGINT field
OR LEFT(RIGHT('00000000000' + CAST(SKU AS varchar(12)),12),11) = @SKU; --CAST incase your SKU field is a INT/BIGINT field
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 4, 2017 at 10:26 am
Thom A - Tuesday, April 4, 2017 10:15 AMWith no sample data, this is a little difficult. At a guess, maybe something like...DECLARE @Newprice decimal(12,2), @SKU varchar(10);
--Assume you provide the SKU so that it has it's leading zero's and no check digit
SET @SKU = '00000012345'; --11 characters
SET @Newprice = 100.99;UPDATE Mytable
SET Price = @Newprice
WHERE RIGHT('0000000000' + CAST(SKU AS varchar(11)),11) = @SKU --CAST incase your SKU field is a INT/BIGINT field
OR LEFT(RIGHT('00000000000' + CAST(SKU AS varchar(12)),12),11) = @SKU; --CAST incase your SKU field is a INT/BIGINT field
Thank you.
The table we will be pulling the Pricing from will be injected into SQL via the Import Data tool, Data Source: Microsoft Excel, Destination: SQL Server native client 11.0 . As when we pull the data from the cash register, its in text/excel format. So, we push it into SQL as a table, then update Price that way.
April 5, 2017 at 1:58 am
Add another column to your table that strips the leading zero and the trailing digit and use that for comparisons. That way you can index it and get decent performance. Otherwise, the solution proposed, which should work, is going to perform poorly because of the calculations on the columns.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply