Aiswarya Aiswarya - 5 months ago 18
SQL Question

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

Answer

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
Comments