July 3, 2018 at 1:02 pm
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 ?
July 4, 2018 at 3:37 pm
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
July 13, 2018 at 2:03 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply