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

Yes, we have to see the starting data (especially the RECORDSEQ column) in order to help. — Tab Alleman, Jun 16 '16 at 12:38

score 1 · Answer 1 · answered Jun 16 '16 at 12:39

1

change your ORDER BY (RECORDSEQ ) to ORDER BY (<column with Quantity in your sample>)

answered Jun 16 '16 at 12:39

xdd

535
2
4

How can I change the query to get AUtonumber based on ProductSKU values and not Quantity?? – Aiswarya Jun 17 '16 at 14:20
The ProductSKU values are constant : 00001597 ,00001600 ; If i make it - order by (Productsku) the Autonumber will repeat right?? – Aiswarya Jun 17 '16 at 15:33
I tried with order by productsku; my results Autonumber ProductSKU Quantity --------- ---------- -------- ABCD123 00001597 42 ABCD124 00001600 42 ABCD123 00001597 35 ABCD124 00001600 35 ABCD123 00001597 39 ABCD124 00001600 39 – Aiswarya Jun 17 '16 at 15:44
you always have 00001597 as ABCD123, and 00001600 as ABCD124 - this is `DENSE_RANK` over your ID's. And when you order by Qty you have result as derieved result in your question. What wrong? – xdd Jun 20 '16 at 07:44

score 0 · Accepted Answer · answered Jun 17 '16 at 16:26

0

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

answered Jun 17 '16 at 16:26

Aiswarya

37
7

Derive Autonumber based on value from another column in SQL

2 Answers2