Link to home
Start Free TrialLog in
Avatar of mburk1968
mburk1968Flag for United States of America

asked on

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

Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

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

Edited by last comment. Added comma.
Avatar of mburk1968

ASKER

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

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

See results attached
Book1.xlsx
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

Same results.
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

I do not. Also it's probably important to mention that I am running SalesOrders one at a time.
FYI I need to step away from desk for about an hour. Thank You
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

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

I get 50001 for all of the SalesOrders in the database
Wait I will create a sample for u
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
See my resiults
Book1.xlsx
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.
We have records before this. The 50000 was simply a made up number. Perhaps I just need to trim the output?
I was able to get the results that I needed by trimming the results.  Thank You