asked
Find Rows Where Column Value Changed
I have a large table that contains daily records over a 30 day period (each customer has 30 records). I need to identify those customers where the "expiration date" has changed, BUT I only want the records where at least one of the dates are in the current month. There could me multiple changes for one customer during the 30 day period. Columns: Customer_Name, Exp_Date Table: CirculationData Examples with current month Sep 2016: I want these records: 1. Expiration date changed from 9-2-2016 to 9-2016 2. Expiration date changed from 9-16-2016 to 8-24-2017 I don't want these records: 1. Expiration date changed from 10-15-2016 to 12-15-2016 Many thanks in advance!!!
Are you saying that a 'change' happens by adding a new record for the Customer_Name? Might be easier to explain if you gave an example of the data in the table, rather than trying to explain in words.
Yes. The table I receive is a mainframe download during nightly processing. Users maintenance customers records on the mainframe. Every customer record is regenerated during processing and a RunDate column value is added accordingly. So, each customer will have 30 records in the table, each with different RunDate. Expiration date changes will be available on the next RunDate record.
Here's an example that uses the LAG() function to compare Exp_Date to previous rows. declare @CirculationData table ( Customer_Name varchar(50), RunDate date, Exp_Date date ) insert into @CirculationData select 'Alice','1 sep 2016','2 sep 2016' insert into @CirculationData select 'Alice','2 sep 2016','2 sep 2016' insert into @CirculationData select 'Alice','3 sep 2016','2 sep 2016' insert into @CirculationData select 'Alice','4 sep 2016','2 sep 2016' insert into @CirculationData select 'Alice','5 sep 2016','2 sep 2016' insert into @CirculationData select 'Bob','1 sep 2016','2 sep 2016' insert into @CirculationData select 'Bob','2 sep 2016','2 sep 2016' insert into @CirculationData select 'Bob','3 sep 2016','3 sep 2016' insert into @CirculationData select 'Bob','4 sep 2016','3 sep 2016' insert into @CirculationData select 'Bob','5 sep 2016','3 sep 2016' insert into @CirculationData select 'Charles','1 sep 2016','9 sep 2016' insert into @CirculationData select 'Charles','2 sep 2016','9 sep 2016' insert into @CirculationData select 'Charles','3 sep 2016','24 Aug 2017' insert into @CirculationData select 'Charles','4 sep 2016','24 Aug 2017' insert into @CirculationData select 'Charles','5 sep 2016','24 Aug 2017' insert into @CirculationData select 'Dave','1 sep 2016','15 oct 2016' insert into @CirculationData select 'Dave','2 sep 2016','15 oct 2016' insert into @CirculationData select 'Dave','3 sep 2016','15 oct 2016' insert into @CirculationData select 'Dave','4 sep 2016','15 dec 2016' insert into @CirculationData select 'Dave','5 sep 2016','15 dec 2016' insert into @CirculationData select 'Eve','1 sep 2016','1 may 2016' insert into @CirculationData select 'Eve','2 sep 2016','12 sep 2016' insert into @CirculationData select 'Eve','3 sep 2016','15 dec 2016' insert into @CirculationData select 'Eve','4 sep 2016','15 dec 2016' insert into @CirculationData select 'Eve','5 sep 2016','15 dec 2016' select t1.Customer_Name, t1.Exp_Date as ChangedFrom, t1.PrevExpDate as ChangedTo from ( select Customer_Name , RunDate , Exp_Date, lag(Exp_Date)over(partition by Customer_Name order by RunDate) as PrevExpDate from @CirculationData )t1 where t1.PrevExpDate is not null and t1.Exp_Date t1.PrevExpDate and ( month(t1.Exp_Date ) = month(getdate()) or month(t1.PrevExpDate ) = month(getdate()) ) Customer_Name ChangedFrom ChangedTo -------------------------------------------------- ----------- ---------- Bob 2016-09-03 2016-09-02 Charles 2017-08-24 2016-09-09 Eve 2016-09-12 2016-05-01 Eve 2016-12-15 2016-09-12 (4 row(s) affected)
17 People are following this question.
