Creating repeated sequential number based on parameter?

Question

i'm essentially trying to create a unique barcode for an invoice in SAP Business one, based on a parameter from the invoice screen and the invoice number.

For the example, the fixed data I will have is "Invoice Number" with a parameter called "Number Of Items"

If the parameter is 10, i want the SQL query to return...

123456 001 010
123456 002 010
123456 003 010
123456 004 010
123456 005 010

etc. This would essentially create me 10 unique numbers which I can then use for a series of barcodes for this invoice? It doesn't need to be inserted into a table, it just needs to be able to calculate that middle dynamic value.

The values i will be querying on are

OINV.InvoiceNumber, OINV.NumberOfParcels FROM OINV

Many Thanks

Andy

"MsSQL studio 17"? That's isn't an RDBMS. *Perhaps you means SQL Server management Studio, which is an IDE for SQL Server? — Thom A
– Thom A ♦, Commented May 10, 2021 at 12:09

Alan Burstein · Accepted Answer · 2021-05-10 12:29:46Z

If you have a numbers table you can simply do this:

DECLARE @SomeNumber INT = 123456,
        @N          INT = 10;

SELECT
  SomeNumber = @SomeNumber,
  Col1       = STUFF('000',3+1-LEN(t.N),LEN(t.N),t.N),
  Col2       = STUFF('000',3+1-LEN(@N),LEN(@N),@N)
FROM  Numbers AS t
WHERE t.N <= @N;

If you don't you could use fnTally. The code would look like this:

SELECT 
  SomeNumber = @SomeNumber,
  Col1       = STUFF('000',3+1-LEN(t.N),LEN(t.N),t.N),
  Col2       = STUFF('000',3+1-LEN(@N),LEN(@N),@N)
FROM   dbo.fnTally(1,@N) AS t;

Both Queries Return:

SomeNumber  Col1    Col2    
----------- ------- ------- 
123456      001     010     
123456      002     010     
123456      003     010     
123456      004     010     
123456      005     010     
123456      006     010     
123456      007     010     
123456      008     010     
123456      009     010     
123456      010     010

Collectives™ on Stack Overflow

Creating repeated sequential number based on parameter?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related