SQL server - Group by Logic

Question

This is my sample data:

Autonumber   ProductSKU    Quantity

---------    ----------    --------

ABCD123      00001597      42

ABCD123      00001600      42

ABCD124      00001597      35

ABCD124      00001600      35

ABCD125      00001597      39

ABCD125      00001600      39

I have to derive the

Autonumber

column values based on the

Quantity

and

ProductSKU

.

Autonumber

should change when

Quantity

&

ProductSKU

changes.
How can I achieve this? I have the logic to increment

Autonumber

but confused with the group by logic. kindly help me with this.

EDIT (copied from OP's post in the answers section):

I have a logic that calculates Autonumber, which is incremented for each row.

Query:

IF @p_channelname ='ABCD'

    BEGIN

            ;WITH CTE_AUTONUMBER_ABCD_AMER AS

                    (SELECT RECORDSEQ

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

                            , AUTONUMBER

                            , LINENUMBER

                , DENSE_RANK()OVER(ORDER BY (RECORDSEQ )) AS LINENUMBER_UPDATE

                    FROM TABLE1 TGT

                    WHERE TGT.[INTERFACE NAME] =@p_interface

                                        AND TGT.[FILENAME]= @p_sourcefilename

                                            AND TGT.CHANNEL = @p_channelname

                                            AND  TGT.GEO='America' 

                    ) UPDATE CTE_AUTONUMBER_ABCD_AMER 

                            SET AUTONUMBER = AUTONUMBER_UPDATE,

                LINENUMBER=LINENUMBER_UPDATE

Currently Autonumber values is :

Autonumber   ProductSKU    Quantity

---------    ----------    --------

ABCD123      00001597      42

ABCD124      00001600      42

ABCD125      00001597      35

ABCD126      00001600      35

ABCD127      00001597      39

ABCD128      00001600      39

I want the Autonumber values to be generated as:

Autonumber   ProductSKU    Quantity

---------    ----------    --------

ABCD123      00001597      42

ABCD123      00001600      42

ABCD124      00001597      35

ABCD124      00001600      35

ABCD125      00001597      39

ABCD125      00001600      39

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Here is a new query, with the logic you want, but it seems pretty crazy for me:

SELECT * INTO TABLE1 FROM (VALUES
(00001597,42,1),(00001600,42,2),(00001597,35,3),(00001600,35,4),(00001597,39,5),
(00001600,39,6)) as x(ProductSKU, Quantity, RECORDSEQ);
GO
;WITH Change as (
SELECT t1.*, 
    CASE WHEN IsNull(t1.ProductSKU,-1) != IsNull(t2.ProductSKU,-1) 
        and IsNull(t1.Quantity,-1) != IsNull(t2.Quantity,-1) 
    THEN 1 ELSE 0 END as Change
FROM TABLE1 as t1
LEFT JOIN TABLE1 as t2 ON t1.RECORDSEQ = t2.RECORDSEQ + 1
)
SELECT 'ABCD' + CAST(122 + RANK() 
    OVER(PARTITION BY Change ORDER BY RECORDSEQ) as VARCHAR) as Autonumber
    , ProductSKU, Quantity
FROM Change
ORDER BY RECORDSEQ;
GO

It returns exactly what you want:

Autonumber  ProductSKU  Quantity
ABCD123     1597        42
ABCD123     1600        42
ABCD124     1597        35
ABCD124     1600        35
ABCD125     1597        39
ABCD125     1600        39

SQL server - Group by Logic

Sign up