x

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!
more ▼

asked Mar 09, 2011 at 04:37 PM in Default

bugs2bugs gravatar image

bugs2bugs
13 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.
more ▼

answered Mar 09, 2011 at 04:57 PM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

Thanks, that worked. I was thinking about something involving rownum but couldn't put this together. I wasn't familiar with "with" either, so that's a new addition to my SQL repertoire.
Mar 10, 2011 at 09:58 AM bugs2bugs
The "with" use there is called a Common Table Expression. They were added to SQL Server 2005 and are extremely helpful in a number of circumstances.
Mar 10, 2011 at 10:31 AM Kevin Feasel
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x371

asked: Mar 09, 2011 at 04:37 PM

Seen: 5006 times

Last Updated: May 21, 2012 at 04:52 PM