Monday 24 October 2011

How to merge Cosecutive duplicate status rows with different values in columns - Sql Server

My Current Table:

Output should be



declare @table table (
       
ProductID int, TransactionID int, Details varchar(50), Status varchar(100), 
       
StatusDate datetime, RequestDate datetime )

insert
into @table 
       
Select 1, 11101, 'Enroll', 'Received'                           , '10/2/2010', '10/01/2010' union all
       
Select 1, 11102, 'Enroll', 'Failed: Invalid Address', '10/4/2010', '10/03/2010' union all
       
Select 1, 11105, 'Update', 'Failed: Invalid Address', '10/5/2010', '10/04/2010' union all
       
Select 1, 11108, 'Update', 'Failed: Invalid Address', '10/6/2010', '10/05/2010' union all
       
Select 1, 11110, 'Update', 'Shipped' , '10/11/2010', '10/10/2010' union all
       
Select 1, 11111, 'Enroll', 'Received'                           , '9/11/2010',  '9/10/2011' union all
       
Select 1, 11113, 'Update', 'Failed: Invalid Address', '9/13/2011',  '9/12/2011' union all
       
Select 1, 11117, 'Update', 'Failed: Invalid Address', '9/14/2011',  '9/13/2011' union all
       
Select 1, 11120, 'Update', 'Shipped'                            , '9/21/2011',  '9/20/2011'
        
;With CTE As (
       
Select *, (Row_Number() over(order by ProductID, TransactionID, StatusDate desc))as RowNum
       
from @table ),
CTE2
As (
       
Select CTE.*, (case When CTE.Status = isnull((select t.Status from CTE t where t.RowNum = (CTE.RowNum + 1)), '')then 1
       
else 0 end
       
)as RowNum2
       
from CTE )Select CTE2.ProductID, CTE2.TransactionID, CTE2.Details, CTE2.Status, CTE2.StatusDate, CTE2.RequestDate
from CTE2 where RowNum2 = 0    

No comments:

Post a Comment