Track Changes in a table on multiple columns

• savioceg

  Right there with Babe

  Points: 795

  More actions

  July 3, 2018 at 1:02 pm

  #414212

  Hi , 
  I have a table as follows 

  drop table if exists #datatable
  create table #datatable
  (
  merchantnumber int ,
  merchantname varchar(100),
  activeflag int,
  address_line1 varchar(100),
  file_date date,
  rec_start_date date,
  rec_end_date date
  )

  insert into #datatable

  select 1001,'ABC Limited',1,'100 Street1','2018-06-01','2018-06-01','2500-01-01'
  union all
  select 1001,'ABC Limited',0,'100 Street1','2018-06-02','2018-06-02','2500-01-01'
  union all

  select 1001,'ABC Limited   #',0,'100 Street1','2018-06-03','2018-06-03','2500-01-01'
  union all
  select 1001,'ABC Limited',0,'100 Street1','2018-06-04','2018-06-04','2500-01-01'
  union all
  select 1001,'ABC Limited',0,'100 Street1','2018-06-05','2018-06-05','2500-01-01'
  union all
  select 1001,'ABC Limited',1,'101 Street1','2018-06-06','2018-06-06','2500-01-01'

  i would like to track the changes on a;; the three fields for a particular merchant number based on the file_date field. The fields that are being used to track changes are merchant name , active flag and address line1. In this case , my expectation is that , only row number 4 and 5 are duplicated in this and i should be able to take the rest with dates so that these are used to track the changes . 

  select *,row_number() over(partition by merchantnumber,merchantname,activeflag,address_line1 order by file_date) rno from #datatable
  order by file_date

  this would return only distinct values in the table . ideally when a vallue changes and then changes back to the original value, i should be able to track that as a change and record the dates . could someone suggest me a solution for this ?

• Evgeny Garaev

  SSCertifiable

  Points: 7428

  More actions

  July 4, 2018 at 3:37 pm

  #1996341

  Something like this?

  
select merchantnumber, max(merchantname) merchantname, max(activeflag) activeflag, min(rec_start_date) rec_start_date, max(rec_end_date) rec_end_date
from #datatable
group by merchantnumber


• drew.allen

  SSC Guru

  Points: 77013

  More actions

  July 13, 2018 at 2:03 pm

  #1997551

  I think you're looking for something like this.

  WITH data_compare AS
(
 SELECT *
 , LAG(merchantname) OVER(PARTITION BY merchantnumber ORDER BY rec_start_date) AS prev_merchant_name
 , LAG(activeflag) OVER(PARTITION BY merchantnumber ORDER BY rec_start_date) AS prev_active_flag
 , LAG(address_line1) OVER(PARTITION BY merchantnumber ORDER BY rec_start_date) AS prev_address_line1
 FROM #datatable
)
SELECT dc.merchantnumber, dc.merchantname, dc.activeflag, dc.address_line1, dc.file_date, dc.rec_start_date, dc.rec_end_date
FROM data_compare dc
CROSS APPLY
(
 SELECT dc.merchantname, dc.activeflag, dc.address_line1

   EXCEPT

   SELECT dc.prev_merchant_name, dc.prev_active_flag, dc.prev_address_line1
  ) diff

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA