Find rows for a unique ID where value changes in any of the columns

  • pwalter83

    SSChampion

    Points: 14557

    Hi,

    Can anybody please help with writing a query to find rows for an ID where value changes in any of the columns.

    Thanks.

     

     

  • handkot

    SSCarpal Tunnel

    Points: 4651

    binary_checksum() ?

    I Have Nine Lives You Have One Only
    THINK!

  • pwalter83

    SSChampion

    Points: 14557

    handkot wrote:

    binary_checksum() ?

    Thanks but I need to display the IDs for all the rows for which the values have changed for 4 columns.

     

  • handkot

    SSCarpal Tunnel

    Points: 4651

    type sample data

    I Have Nine Lives You Have One Only
    THINK!

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    if you add a computed column with a checksum or md5 hash then add a 2nd column that is previous checksum with a trigger dealing with the copy into old checksum. maybe add a datechanged field...

    should be easy to build a query

    or just add a trigger that checks only those 4 columns and logs it to another table (or a status column) - either way it does what you are suggesting

     

     

     

     

    MVDBA

  • pwalter83

    SSChampion

    Points: 14557

    handkot wrote:

    type sample data

    Please find the DDL and sample data below.

    the requirement is to display the product_ID for the row where the values have changed for the following 5 columns:

    Main_Product

    Product_Colour

    Product_Size

    Tax_Class_ID

    Outer_Print_Name

    Thanks for your help.

    CREATE TABLE Catalog_Data(
    [Product_Catalog_ID] [int] IDENTITY(1,1) NOT NULL,
    [Main_Product] [varchar](50) NULL,
    [Product_Colour] [varchar](50) NULL,
    [Product_Size] [varchar](50) NULL,
    [Tax_Class_ID] [varchar](10) NULL,
    [Outer_Print_Name] [varchar](100) NULL
    ) ON [PRIMARY]

    Insert into Catalog_Data
    select '245862118101DRK_SGE', 'Dark Sage', 'One Size', 'standard','Provence'

    union all
    select '080482153116NAVY','Navy','One Size','standard','Button Spot'
    Union all
    select '404999999005BLUE_4', 'Sheer Blue','39','exempt','Block Check'
    select '184372049100IVORY', 'Ivory','1-2 yr','exempt','Streets'
    Union all
    select '525478545852', 'Oyster Shell', 'One Size','standard','Guards'

    • This reply was modified 2 months, 3 weeks ago by  pwalter83.
    • This reply was modified 2 months, 3 weeks ago by  pwalter83.
  • handkot

    SSCarpal Tunnel

    Points: 4651

    MVDBA (Mike Vessey) gave a good idea

    I meant something like this

    CREATE TABLE #Catalog_Data(

    [Product_Catalog_ID] [int] IDENTITY(1,1) NOT NULL,

    [Main_Product] [varchar](50) NULL,

    [Product_Colour] [varchar](50) NULL,

    [Product_Size] [varchar](50) NULL,

    [Tax_Class_ID] [varchar](10) NULL,

    [Outer_Print_Name] [varchar](100) NULL

    )

    Insert into #Catalog_Data

    select '245862118101DRK_SGE', 'Dark Sage', 'One Size', 'standard','Provence'

    union all

    select '080482153116NAVY','Navy','One Size','standard','Button Spot'

    Union all

    select '404999999005BLUE_4', 'Sheer Blue','39','exempt','Block Check'

    union all

    select '184372049100IVORY', 'Ivory','1-2 yr','exempt','Streets'

    Union all

    select '525478545852', 'Oyster Shell', 'One Size','standard','Guards'

     

    CREATE TABLE #Catalog_Data_New(

    [Product_Catalog_ID] Int,

    [Main_Product] [varchar](50) NULL,

    [Product_Colour] [varchar](50) NULL,

    [Product_Size] [varchar](50) NULL,

    [Tax_Class_ID] [varchar](10) NULL,

    [Outer_Print_Name] [varchar](100) NULL

    )

    Insert into #Catalog_Data_New

    select top (2) * from #Catalog_Data

    update top (1) #Catalog_Data_New set [Main_Product] = [Main_Product] + 'new'

    update top (1) #Catalog_Data_New set [Outer_Print_Name] = [Outer_Print_Name] + 'new'

    select * from #Catalog_Data

    select * from #Catalog_Data_New

    Select

    *

    From

    #Catalog_Data d

    Join #Catalog_Data_New dn On dn.Product_Catalog_ID = d.Product_Catalog_ID

    And BINARY_CHECKSUM(dn.Main_Product,dn.Product_Colour,dn.Product_Size,dn.Tax_Class_ID,dn.Outer_Print_Name)

    <> BINARY_CHECKSUM(d.Main_Product,d.Product_Colour,d.Product_Size,d.Tax_Class_ID,d.Outer_Print_Name)

     

    P.S.: last time i usually use   Temporal tables for logging

    I Have Nine Lives You Have One Only
    THINK!

  • Jeff Moden

    SSC Guru

    Points: 996502

    pwalter83 wrote:

    handkot wrote:

    type sample data

    Please find the DDL and sample data below.

    the requirement is to display the product_ID for the row where the values have changed for the following 5 columns:

    Main_Product

    Product_Colour

    Product_Size

    Tax_Class_ID

    Outer_Print_Name

    Thanks for your help.

    CREATE TABLE Catalog_Data(
    [Product_Catalog_ID] [int] IDENTITY(1,1) NOT NULL,
    [Main_Product] [varchar](50) NULL,
    [Product_Colour] [varchar](50) NULL,
    [Product_Size] [varchar](50) NULL,
    [Tax_Class_ID] [varchar](10) NULL,
    [Outer_Print_Name] [varchar](100) NULL
    ) ON [PRIMARY]

    Insert into Catalog_Data
    select '245862118101DRK_SGE', 'Dark Sage', 'One Size', 'standard','Provence'

    union all
    select '080482153116NAVY','Navy','One Size','standard','Button Spot'
    Union all
    select '404999999005BLUE_4', 'Sheer Blue','39','exempt','Block Check'
    select '184372049100IVORY', 'Ivory','1-2 yr','exempt','Streets'
    Union all
    select '525478545852', 'Oyster Shell', 'One Size','standard','Guards'

    I'm not seeing any differences in the table of data you provided above for any Main_Product because all of the Main_Products are unique above.

    Are you trying to compare one table to another to find differences or ???

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply