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
AutonumberQuantityProductSKUAutonumberQuantityProductSKUHow can I achieve this? I have the logic to increment
AutonumberEDIT (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
Source (Stackoverflow)