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