locked
Ordered Records - Check for Codes RRS feed

  • Question

  • User-781338610 posted

    Hi,

    I have been given a dataset of 150k plus records:

    ID OrderNo Code
    1 1 ABC
    1 1 XYZ
    2 1 DEF
    2 2 JKL
    3 1 ABC
    3 1 XYZ
    5 1 LMN
    5 2 TUS
    5 3 EFG
    6 1 LMN
    6 1 XYZ
    7 1 HIJ
    7 2 UVW
    7 2 XYZ
    7 3 JKL

    When there is an occurrence of 'XYZ' and there is a matching OrderNo for the same ID, I need to alter the Code of the corresponding same OrderNo. 

    So, for example, in the above table I need to update the Code for record ID 1 and Orderno 1 to ABC-1 as there is a  Code is XYZ for the same ID.

    Additionally for, record ID 7 there is an occurrence of XYZ and there is a identical OrderNo of 2, I need to update the Code UVW to UVW-1.

    I hope this makes sense.

    Any help is greatly appreciated.

    Fiorano

    Monday, November 14, 2016 11:14 AM

All replies

  • User753101303 posted

    Hi,

    Not sure how general it needs to be. It is only XYZ or could it be something else ? XYZ should stay unchanged ? Can you have more than 2 rows for a single (ID,OrderID) couple?

    For now I wonder if you don't just want to number your rows? If yes, have a look at https://msdn.microsoft.com/en-us/library/ms186734.aspx and start maybe with :

    SELECT *,ROW_NUMBER() OVER(PARTITION BY Id,OrderID ORDER BY Id,OrderId,Code)
    FROM YourTable

    Monday, November 14, 2016 12:02 PM
  • User-967720686 posted

    Hi, Try the code below

    Declare @Table Table (ID Int, OrderNo Int, Code Varchar(5))
    
    Insert	Into @Table (ID, OrderNo, Code)
    Values	(1,	1,	'ABC'),
    		(1,	1,	'XYZ'),
    		(2,	1,	'DEF'),
    		(2,	2,	'JKL'),
    		(3,	1,	'ABC'),
    		(3,	1,	'XYZ'),
    		(5,	1,	'LMN'),
    		(5,	2,	'TUS'),
    		(5,	3,	'EFG'),
    		(6,	1,	'LMN'),
    		(6,	1,	'XYZ'),
    		(7,	1,	'HIJ'),
    		(7,	2,	'UVW'),
    		(7,	2,	'XYZ'),
    		(7,	3,	'JKL')
    		
    -- If only selection requried 
    Select  T.Id, T.OrderNo, Case When Tx.Id Is Not Null Then (T.Code + '-1') Else T.Code End As 
    From	@Table T
    		Left Join (
    			Select Id, OrderNo, Code From @Table Where Code = 'XYZ'
    		) Tx on T.ID = Tx.ID And T.OrderNo = Tx.OrderNo And T.Code != Tx.Code 
    
    
    -- If needed to update the source table 
    Update	T
    Set		T.Code = Case When Tx.Id Is Not Null Then T.Code + '-1' Else T.Code End
    From	@Table T
    		Left Join (
    			Select Id, OrderNo From @Table Where Code = 'XYZ'
    		) Tx on T.ID = Tx.ID And T.OrderNo = Tx.OrderNo 
    Where	T.Code != 'XYZ'		
    
    
    Select * From @Table 

    Monday, November 14, 2016 11:46 PM