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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.