query to find when a value changes from one row to the next?
My data looks like this: Date Amount Jan 1 100 Feb 1 100 Mar 1 150 Apr 1 150 May 1 100 Jun 1 100 I need a query that just shows the date/amount when the amount changes: Date Amount Jan 1 100 Mar 1 150 May 1 100 Initially I was just using group by and finding the minimum date, but the amount could change back to a previous value, so it could be a little tricky. Thanks!
Here's a way to do it using a windowing function (ROW_NUMBER in this case): create table #DataTable ( DateOfEntry datetime, Amount int ) insert into #DataTable values('2011-01-01', 100); insert into #DataTable values('2011-02-01', 100); insert into #DataTable values('2011-03-01', 150); insert into #DataTable values('2011-04-01', 150); insert into #DataTable values('2011-05-01', 100); insert into #DataTable values('2011-06-01', 200); insert into #DataTable values('2011-07-01', 600); insert into #DataTable values('2011-08-01', 600); with records as ( select DateOfEntry, Amount, ROW_NUMBER() over (order by DateOfEntry) as RowNum from #DataTable ) select r1.* from records r1 LEFT OUTER JOIN records r2 on r1.RowNum = r2.RowNum + 1 where r2.RowNum IS NULL OR r2.Amount r1.Amount; drop table #DataTable; In this example, we're getting row numbers based on the date. ROW_NUMBER will increment with no repeats and no gaps, so you can join the CTE (in 2005; if you're using 2000, turn records into a subquery) on the row number, as you want to see if the current record differs from the previous record. If it does, mark the change. Also, because the first record won't join to anything, include it as well--you change from nothing to something.