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

Question

Post reply

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

pwalter83

SSChampion

Points: 14575
More actions
March 13, 2020 at 11:06 am

#3733990

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

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

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

handkot SSCertifiable Points: 5259 More actions · Answer 1

binary_checksum() ?

I Have Nine Lives You Have One Only
THINK!

pwalter83 SSChampion Points: 14575 More actions · Answer 2

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 SSCertifiable Points: 5259 More actions · Answer 3

type sample data

I Have Nine Lives You Have One Only
THINK!

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

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: 14575 More actions · Answer 5

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 4 years, 9 months ago by pwalter83.
This reply was modified 4 years, 9 months ago by pwalter83.

handkot SSCertifiable Points: 5259 More actions · Answer 6

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: 1004403 More actions · Answer 7

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)