SQL Syntax increment a number each time I execute the query

I am creating a Sales Report that exports to CSV to be imported into an EDI system. The query pulls detail records using @SalesOrder as the parameter. I have a need to increment a shipment number and Container ID for each SalesOrder # the query executes against. This is just a temporary solution to move inventory. Once completed I'll have no need for this. So I'm looking for the easiest solution.

DECLARE @SalesOrder INT;
SET @SalesOrder = '830901';
SELECT  DTL = 'DTL' ,
        ShipmentNumber = 'Begin with 50000 and increment' ,
		ContainerID = 'Begin with PORTCONTAINER1 and increment',
        ORD.style ,
        ORD.color_code ,
        ORD.dimension
FROM    [dataKLL].[dbo].[KLL Sales Order Summary] ORD
WHERE   ord_num = @SalesOrder;

Open in new window

mburk1968Asked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
sorry that was not correct. Please try tested solution below  -

Data Generation

CREATE TABLE [KLL Sales Order Summary]
(
	 style	INT	
	,color_code		VARCHAR(10)
	,dimension	 VARCHAR(10)
	,ord_num INT
)
GO

INSERT INTO [KLL Sales Order Summary] VALUES
(1150404,'MULTI','06D00',830901),                                           	
(1163120,'BLUE' ,'06C00', 830901),                                           	
(2170355,'TE/MU','06D00',830901),                                           	
(2153138,'MULTI','06V00',830901),                                           	
(3160035,'WHITE','06D00',830902),                                           	
(2170630,'WH/BK','06D00',830902),                                           	
(3160035,'WHITE','06E00',830902),                                           	
(3165636,'PK/BK','06D00',830903)
GO       

Open in new window


Solution

DECLARE @SalesOrder INT;
SET @SalesOrder = '830902';
;WITH CTE AS
(
	SELECT  DTL = 'DTL' ,
			ShipmentNumber = 50000 +  DENSE_RANK() OVER (ORDER BY ord_num) ,
			ContainerID =  'PORTCONTAINER' + CAST(DENSE_RANK() OVER  (ORDER BY ord_num) AS VARCHAR(100)) ,
			ORD.style ,
			ORD.color_code ,
			ORD.dimension,
			ord_num
	FROM    [KLL Sales Order Summary] ORD	
)
SELECT * FROM CTE
WHERE ord_num = @SalesOrder

Open in new window


Output

/*------------------------
OUTPUT
------------------------*/
DTL  ShipmentNumber       ContainerID        style       color_code dimension  ord_num
---- -------------------- ------------------ ----------- ---------- ---------- -----------
DTL  50002                PORTCONTAINER2     3160035     WHITE      06D00      830902
DTL  50002                PORTCONTAINER2     2170630     WH/BK      06D00      830902
DTL  50002                PORTCONTAINER2     3160035     WHITE      06E00      830902

(3 row(s) affected)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Please try this-

DECLARE @SalesOrder INT;
SET @SalesOrder = '830901';

SELECT  DTL = 'DTL' ,
        ShipmentNumber = 50000 +  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) ,
		ContainerID =  ROW_NUMBER() OVER (ORDER BY ContainerID ASC),
        ORD.style ,
        ORD.color_code ,
        ORD.dimension
FROM    [dataKLL].[dbo].[KLL Sales Order Summary] ORD
WHERE   ord_num = @SalesOrder;

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Edited by last comment. Added comma.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mburk1968Author Commented:
Sorry I should have be a bit more clear in my request. the ShipmentNumber for the 18 detail records returned for SalesOrder# 830901 should all be 50000. What I am getting is 50000, 50001, 50002, etc.

So for example if I execute for SalesOrder# 830901 I get 50000 for all the DTL Records returned. If I run for SalesOrder# 830902 I would get 50001, etc. Does that make sense?

Also I'm getting an error for ContainerID 'Invalid column name.  and the Container ID should work similar to the ShipmentNumber only it should be PORTCONTAINER1 for SalesOrder# 830902 and if I were to run for another SalesOrder it would be PORTCONTAINER2 for all DTL records returned.
0
 
Pawan KumarDatabase ExpertCommented:
Updated ..
DECLARE @SalesOrder INT;
SET @SalesOrder = '830901';

SELECT  DTL = 'DTL' ,
        ShipmentNumber = 50000 +  DENSE_RANK() OVER (ORDER BY (SELECT NULL)) ,
		ContainerID =  'PORTCONTAINER' + CAST(DENSE_RANK() OVER  (ORDER BY (SELECT NULL)) AS VARCHAR(100)) ,
        ORD.style ,
        ORD.color_code ,
        ORD.dimension
FROM    [dataKLL].[dbo].[KLL Sales Order Summary] ORD
WHERE   ord_num = @SalesOrder

Open in new window

0
 
mburk1968Author Commented:
I get the format that I'm looking for however I get the same values if I run for a different sales order. I need them to be unique for each SalesOrder that I execute the query against.

So I'm thinking this will need to be put into a table right?
0
 
Pawan KumarDatabase ExpertCommented:
Please run this and see what you are getting..

SELECT  DTL = 'DTL' ,
        ShipmentNumber = 50000 +  DENSE_RANK() OVER (ORDER BY (SELECT NULL)) ,
		ContainerID =  'PORTCONTAINER' + CAST(DENSE_RANK() OVER  (ORDER BY (SELECT NULL)) AS VARCHAR(100)) ,
        ORD.style ,
        ORD.color_code ,
        ORD.dimension
FROM    [dataKLL].[dbo].[KLL Sales Order Summary] ORD

Open in new window

0
 
mburk1968Author Commented:
See results attached
Book1.xlsx
0
 
Pawan KumarDatabase ExpertCommented:
Please try this-

DECLARE @SalesOrder INT;
SET @SalesOrder = '830901';

;WITH CTE AS
(
	SELECT  DTL = 'DTL' ,
			ShipmentNumber = 50000 +  DENSE_RANK() OVER (ORDER BY (SELECT NULL)) ,
			ContainerID =  'PORTCONTAINER' + CAST(DENSE_RANK() OVER  (ORDER BY (SELECT NULL)) AS VARCHAR(100)) ,
			ORD.style ,
			ORD.color_code ,
			ORD.dimension,
			ord_num
	FROM    [dataKLL].[dbo].[KLL Sales Order Summary] ORD	
)
SELECT * FROM CTE
WHERE ord_num = @SalesOrder

Open in new window

0
 
mburk1968Author Commented:
Same results.
0
 
Pawan KumarDatabase ExpertCommented:
Ok if you are running query below are you getting 50002 with SalesOrder = 830902 ??            
                                                                        
SELECT  DTL = 'DTL' ,
			ShipmentNumber = 50000 +  DENSE_RANK() OVER (ORDER BY (SELECT NULL)) ,
			ContainerID =  'PORTCONTAINER' + CAST(DENSE_RANK() OVER  (ORDER BY (SELECT NULL)) AS VARCHAR(100)) ,
			ORD.style ,
			ORD.color_code ,
			ORD.dimension,
			ord_num
	FROM    [dataKLL].[dbo].[KLL Sales Order Summary] ORD

Open in new window

0
 
mburk1968Author Commented:
I do not. Also it's probably important to mention that I am running SalesOrders one at a time.
0
 
mburk1968Author Commented:
FYI I need to step away from desk for about an hour. Thank You
0
 
Pawan KumarDatabase ExpertCommented:
Pls do not use any filter. Please run as it is..Also if possible pls provide that data to excel.

;WITH CTE AS
(
	SELECT  DTL = 'DTL' ,
			ShipmentNumber = 50000 +  DENSE_RANK() OVER (ORDER BY (SELECT NULL)) ,
			ContainerID =  'PORTCONTAINER' + CAST(DENSE_RANK() OVER  (ORDER BY (SELECT NULL)) AS VARCHAR(100)) ,
			ORD.style ,
			ORD.color_code ,
			ORD.dimension,
			ord_num
	FROM    [dataKLL].[dbo].[KLL Sales Order Summary] ORD	
)
SELECT * FROM CTE

Open in new window

0
 
mburk1968Author Commented:
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '.'.

When I click the error it take me here         SELECT * FROM CTE
        ORD.style ,


If I remove the ORD it gives me incorrect Column Color_Code
0
 
Pawan KumarDatabase ExpertCommented:
pls run this -

SELECT  DTL = 'DTL' ,
			ShipmentNumber = 50000 +  DENSE_RANK() OVER (ORDER BY (SELECT NULL)) ,
			ContainerID =  'PORTCONTAINER' + CAST(DENSE_RANK() OVER  (ORDER BY (SELECT NULL)) AS VARCHAR(100)) ,
			ORD.style ,
			ORD.color_code ,
			ORD.dimension,
			ord_num
	FROM    [dataKLL].[dbo].[KLL Sales Order Summary] ORD

Open in new window

0
 
mburk1968Author Commented:
I get 50001 for all of the SalesOrders in the database
0
 
Pawan KumarDatabase ExpertCommented:
Wait I will create a sample for u
0
 
mburk1968Author Commented:
See my resiults
Book1.xlsx
0
 
Pawan KumarDatabase ExpertCommented:
Do we have  starting from SalesOrder = 830901 or do we have records before this also ?

Can you send the entire in excel to me ..so that i can write the tested query. thanks.
0
 
mburk1968Author Commented:
We have records before this. The 50000 was simply a made up number. Perhaps I just need to trim the output?
0
 
mburk1968Author Commented:
I was able to get the results that I needed by trimming the results.  Thank You
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.