Order Number Generation Based On Distributor Code-CodePudding

I need to generate Order number as below scenarios based on Distributor Code. Order number format will be as below

Order Number: Distributor Code/Retailer Code/TO/DDMMYYOrder number(Order number will be 0001,0002,...)

Scenario 1  Order Uploaded Console time: 11AM (08-Jan'22)
Retailer 1  22520/1400002/TO/0801220001
Retailer 2  22520/1400003/TO/0801220002
Retailer 3  22520/1400004/TO/0801220003
Retailer 4  22520/1400005/TO/0801220004
Retailer 5  22520/1400006/TO/0801220005
Retailer 6  22520/1400007/TO/0801220006

Same Distributor different retailers. Order number carries forward Distributor  wise and not retailer wise.

AND

Scenario 2  Order Uploaded Console time 2PM (08-Jan'22)
Retailer 1  22520/1400002/TO/0801220007
Retailer 2  22520/1400003/TO/0801220008
Retailer 7  22520/1400011/TO/0801220009
Retailer 8  22520/1400012/TO/0801220010

Same Distributor different retailers and same retailers. For same retailers for the "same product" same or unique quantity gets uploaded.

AND

Scenario 3  Order Uploaded Console time 10AM (14-Jan'22)
Retailer 4  22520/1400005/TO/1401220011
Retailer 5  22520/1400006/TO/1401220012
Retailer 6  22520/1400007/TO/1401220013
Retailer 8  22520/1400012/TO/1401220014

Same Distributor different day order was uploaded. Please note the series

I have uploaded a some data from EXCEL to DATABASE using below SP

CREATE PROC [dbo].[Proc_TeleOrderUpload]    
(                              
@ORDER_UPLOAD NTEXT = NULL,              
@UserId INT = 0                                                     
)                        
AS                                 
BEGIN
DECLARE @idoc AS INT
    Create Table #Tbl_TeleOrderUpload
    (
        DistCode NVARCHAR(50),  
        CompRtrCode NVARCHAR(50),                       
        ProductCode NVARCHAR(50),
        QtyInPieces Varchar(10),
        errStatus INT
    )

     EXEC sp_xml_preparedocument @idoc OUTPUT, @ORDER_UPLOAD

     INSERT INTO #Tbl_TeleOrderUpload
     SELECT  
        DistCode,   
        CompRtrCode,                       
        ProductCode,
        QtyInPieces,
        0 AS errStatus    
    FROM OPENXML (@idoc, '/TeleOrderUpload/Record',2)
    WITH                           
    (                
        DistCode NVARCHAR(50),  
        CompRtrCode NVARCHAR(50),                       
        ProductCode NVARCHAR(50),
        QtyInPieces Varchar(10)
    )

    EXEC sp_xml_removedocument @idoc

      INSERT INTO Tbl_TeleOrderUpload
        (
            DistCode,
            CompRtrCode,
            ProductCode,
            QtyInPieces,
            OrderTaken,
            OrderNumber,
            UploadFlag,
            CreatedId,
            CreatedDate  
        )
        SELECT DISTINCT 
            DistCode,
            CompRtrCode,
            ProductCode,
            CAST(QtyInPieces AS int),
            'Console',
            '',
            'Y',
            @UserId,    
            GETDATE()
        FROM
         #Tbl_TeleOrderUpload

         SELECT 'DATA UPLOADED SUCCESSFULLY...'
END

In this, i need to insert the order number as above. Here cursor should not used to update the order number. I need to implement the below logic

DECLARE @number AS INT =  0
SET @number = (SELECT MAX(RIGHT(OrderNumber,6)) FROM Tbl_TeleOrderUpload WHERE DistCode=@DistCode)

IF @number IS NULL
SET @number=1
ELSE
SET @number =@number 1

SELECT RIGHT('000000'   CAST(@number AS VARCHAR(6)) , 6)

Sample data is as follows:

CREATE TABLE [dbo].[Tbl_TeleOrderUpload](
[DistCode] [nvarchar](50) NULL,
[CompRtrCode] [nvarchar](50) NULL,
[ProductCode] [nvarchar](50) NULL,
[QtyInPieces] [int] NULL,
[OrderTaken] [varchar](10) NULL,
[OrderNumber] [varchar](100) NULL,
[UploadFlag] [varchar](10) NULL,
[CreatedId] [int] NULL,
[CreatedDate] [datetime] NULL
)


INSERT INTO Tbl_TeleOrderUpload VALUES ('0000014811','148111600220','GNOTNA06',1,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000014811','148111600220','GNOTNA06',2,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000014811','148111600220','GNOTNA06',3,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000014811','148111600220','GNOTNA06',4,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000014811','148111600220','GNOTNA06',5,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000015000','15001600220','GNOTNA06',1,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000015000','150011600220','GNOTNA06',2,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000015000','15001600220','GNOTNA06',3,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000015000','15001600220','GNOTNA06',4,'Console','','Y',1,GETDATE())
INSERT INTO Tbl_TeleOrderUpload VALUES ('0000015000','150011600220','GNOTNA06',5,'Console','','Y',1,GETDATE())

Please guide me to achieve this.

My SQL SERVER Version is

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

Thanks in advance

CodePudding user response：

Looks like ROW_NUMBER() works in SQL 2008 so possibly this is what you want:

SELECT *, RIGHT('000000'   CAST(@MyOrderNumber AS VARCHAR(6)) , 6)
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY DistCode ORDER BY CompRtrCode) As MyOrderNumber
    FROM Table
) ST

I'm sure there is far more to your requirement than this.

This will generate some order numbers from a statric dataset. You can use this to go an update the table if you like.

But it doesn't account for new records appear. and it definitely doesn't account for when new records appear in the middle of existing records

This is why it's a bad idea to have special artifically generated id's like this.

If you want to be able to base this on existing records and have the order number increment from there, the solution is more complicated.

SELECT ST.*, RIGHT('000000'   CAST(ST.MyOrderNumber   ISNULL(CN.HighestOrderNo) AS VARCHAR(6)) , 6)
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY DistCode ORDER BY CompRtrCode) As MyOrderNumber
    FROM Table
) ST
LEFT JOIN
(
  SELECT DistCode, MAX(ExistingOrderNo) HighestOrderNo
  GROUP BY DistCode 
    FROM Table
) CN
ON CN.DistCode = ST.DistCode

But I have to reiterate:

It's a bad idea to generate keys based on special rules like this because you end up with gaps and inconsistent numbers as different records arrive.

Specifically this case:

Retailer 4  22520/1400005/TO/1401220011
Retailer 5  22520/1400006/TO/1401220012
Retailer 6  22520/1400007/TO/1401220013
Retailer 8  22520/1400012/TO/1401220014

Now you import the next batch of data which arrives the same day and it contains this record:

Retailer 6  22520/1400008/TO/140122

What order number should it have? 1400008 is after 140007, so it should be 000014. But that's already taken.