We frequently have to generate location or item codes for clients. This is typically a sequential number that has multiple levels in the code. For example, there are 4 carousels; each carousel has either 23 or 46 carriages (a column of shelving); each carriage has 11 shelves; each shelf has either 3 or 6 possible locations. We need barcodes for all possible locations.
Here is a sample of what this would look like for Carousel 1:
The issue here was I needed the data for the Carriage column to count from 01 to 46, but I needed each of those 46 numbers to be repeated 51 times. Because there are 11 shelves with 3 to 6 locations per shelf there are a total of 51 locations per Carriage.
Before I go further, I realize there are a number of ways to get this done. What follows was my simple and quick solution.
I decided to write a loop in a SQL Server query to generate my data.
Here is the SQL;
DECLARE @Val1 INT DECLARE @Val2 INT DECLARE @StrLen TINYINT SET @StrLen = 2 SET @Val2 = 1 WHILE @Val2 <= 46 BEGIN SET @Val1 = 1 WHILE @Val1 <= 51 BEGIN IF LEN(@Val2) > @StrLen PRINT @Val2 ELSE PRINT Replace(Str(@Val2, @StrLen), ' ', '0') SET @Val1 = @Val1 + 1 END SET @Val2 = @Val2 + 1 END
Val1 = The number of times you want each number repeated.
Val2 = The total range of numbers you want to be generated.
For example, if you set Val1 to go from 1 to 51 and Val2 to go from 1 to 46 you will get 1 through 46 with each number repeated 51 times.
StrLen= The minimum length of the returned number. This will pad with 0 to make the returned value = whatever you set StrLen to. For example, if you set StrLen to 2, 1 – 9 would be 01 – 09. We do a check to see if the length of Val2 is > StrLen, if it is we print the value of Val2 without trying to pad it. That way 101 doesn’t come back as ** because it’s too long for StrLen.
The output looks something like this:
From here I can copy / paste the data into Excel where I then use a concatenate formula to generate my “Code” column data.