Derive Autonumber based on value from another column in SQL

Question

I have a SSIS package which has Stored procedure feed in the backend.I have to derive the autonumber logic for this ChannelName. My current code is:

IF @p_channelname ='ABCD'
BEGIN
        ;WITH CTE_AUTONUMBER_PV_AMER AS
                (SELECT RECORDSEQ
                        , 'ABCD'+CAST(@PrevOrderID+DENSE_RANK()OVER(ORDER BY (RECORDSEQ )) AS NVARCHAR(MAX)) AS AUTONUMBER_UPDATE
                        , AUTONUMBER
                FROM IC_CTXS_TRANSACTION_SOURCEFEED TGT
                WHERE TGT.[INTERFACE NAME] =@p_interface
                                    AND TGT.[FILENAME]= @p_sourcefilename
                                        AND TGT.CHANNEL = @p_channelname
                                        AND  TGT.GEO='America' 
                ) UPDATE CTE_AUTONUMBER_PV_AMER 
                        SET AUTONUMBER = AUTONUMBER_UPDATE

The result is :

Autonumber   ProductSKU    Quantity
---------    ----------    --------
ABCD123      00001597      42
ABCD124      00001600      42
ABCD125      00001597      35
ABCD126      00001600      35
ABCD127      00001597      39
ABCD128      00001600      39

Here the '123'(@PrevOrderID) is from a previous file.

I want to derive the following results:

Autonumber   ProductSKU    Quantity
---------    ----------    --------
ABCD123      00001597      42
ABCD123      00001600      42
ABCD124      00001597      35
ABCD124      00001600      35
ABCD125      00001597      39
ABCD125      00001600      39

The Product SKU's : 00001597 and 00001600 will be constant values.

How can I change the code to get this result?

TIA :)

Answer 1

I got the answer :) Used row_number instead of Dense_rank:

Query change

, 'ABCD'+CAST(@PrevOrderID+ROW_NUMBER()OVER(PARTITION BY PRODUCTSKU ORDER BY (RECORDSEQ )) AS NVARCHAR(MAX)) AS AUTONUMBER_UPDATE