mercredi 15 juin 2016

SQL server - Group by Logic

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

This is the current query: ;WITH CTE_AUTONUMBER_EPIC_AMER AS (SELECT RECORDSEQ ,CASE WHEN @P_CHANNELNAME ='Epic' THEN 'Epic'+CAST(CAST(AUTONUMBER AS INT) AS NVARCHAR(MAX))
END AS AUTONUMBER_UPDATE , AUTONUMBER , LINENUMBER , DENSE_RANK()OVER(ORDER BY (RECORDSEQ )) AS LINENUMBER_UPDATE 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' AND ISNUMERIC(CAST(AUTONUMBER AS INT))=1 AND AUTONUMBER IS NOT NULL ) UPDATE CTE_AUTONUMBER_EPIC_AMER SET AUTONUMBER = AUTONUMBER_UPDATE, LINENUMBER = LINENUMBER_UPDATE

How can I include the logic in this without disturbing the structure of the query?

Aucun commentaire:

Enregistrer un commentaire