My data looks like this:
I need a query that just shows the date/amount when the amount changes:
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!
asked Mar 09 '11 at 04:37 PM in Default
Here's a way to do it using a windowing function (ROW_NUMBER in this case):
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.
answered Mar 09 '11 at 04:57 PM