I am trying to create labels based on a batches quantity. I have the query mostly compete except I can not figure out how to create multiple rows of the same data based on the number value in a column. I need the query to select from my temp table I've created the BatchID number and then generated rows based on the number value in the items column for create the duplicate rows in order to generate the labels. This is the query I have so far.
CREATE TABLE #Temp_Table
(
BatchID INT
,Received_Date DATE
,Batched_Date DATE
,Batch_Item_Count INT
,Location# INT
,Doc_Description VARCHAR(MAX)
)
INSERT INTO #Temp_Table
Currently the query gives only 1 row of results and should give multiple rows. I also need to format the rows to create a sequence that starts at 000 and then generates a sequence number for those rows. So if I have 12 items in a batch the query generates 12 rows with same BatchID and then runs a a sequence to generate document numbers starting from 0 formatted to look like 000, 001, 002, 003, 004 and so until you reach 011 which would be 12 items in the batch. Sorry I didn't type this before forgot about the formatted sequence number needed on the labels.
You didn't provide any sample data, so I assume you won't take offense if I do it for you.
CREATE TABLE #Temp_Table
(
BatchID INT
,Received_Date DATE
,Batched_Date DATE
,Batch_Item_Count INT
,Location# INT
,Doc_Description VARCHAR(MAX)
)
INSERT INTO #Temp_Table
VALUES
(1,GETDATE(), GETDATE(), 10, 1, 'A test Row'),
(2,GETDATE(), GETDATE(), 14, 1, 'Another test Row');
It's not clear to me if you have to reset the document number for each batch. If I understand correctly this is your requirement.
You also didn't tell us which column contains the quantity you want to use to multiply rows. I assumed it is Batch_Item_Count.
What you need here is a Tally Table: a table that contains numbers. If you don't have one, you can create it on the fly using some other table to pull the numbers from. In this case I used sys.all_columns.
WITH InlineTally AS (
SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac -- probably enough if quantities are not huge
)
SELECT *,
document_number = RIGHT('000' + CAST(ROW_NUMBER() OVER(PARTITION BY BatchId ORDER BY N) -1 AS varchar(3)),3)
FROM dbo.#Temp_Table tt
INNER JOIN InlineTally AS it
ON tt.Batch_Item_Count >= it.N
I will try this today, sorry about limited info. I am still new to SQL and trying to get the hang of programming. Thank you for your response and no offense taken.
Thank you for the help, I was finally able to get the query to work when bitsmed suggestion, and I also found an error I had from when I tired to copy your script.
I'm also looking to do this. I'm getting an error with the Varchar.
In this case I'm doing: UPC | Qty instead of: BatchID I Batch_Item_Count
WITH InlineTally AS (
SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac -- probably enough if quantities are not huge
)
SELECT
ma.UPC,
document_number = RIGHT('000' + CAST(ROW_NUMBER() OVER(PARTITION BY UPC ORDER BY N) -1 AS Varchar(3)),3)
FROM tmp_tho_mach ma
INNER JOIN InlineTally AS it
ON ma.Qty >= it.N
^^ Context, not really sure how to but I'm trying to generate new rows where the UPC and the Qty is concated but for each Qty value. I.e., so 886661461004-10, 886661461004-9, 886661461004-8, 886661461004-7
I keep getting errors with what was described above.