Thursday 12 September 2013

How to query to get only rows where a change took place? (changes can go back and forth)

How to query to get only rows where a change took place? (changes can go
back and forth)

I'm working with a table that has dozens of rows per customer, each with a
date and several columns representing various statuses. I'm only
interested in pulling the rows where a change took place in one particular
column (specifically 0 to 1 or 1 to 0, see status column below).
I can't simply use row_number() over (partition by customer_id, status
order by date) because the status can go back and forth between 0 and 1.
Here's a sample of what I'm trying to do (note that there are two
different Customer IDs in this example):
Original Table
Row Customer ID Status Date 1 123 0 3/12/2013 2 123 0 3/31/2013 3 123 1
4/13/2013 4 123 1 4/15/2013 5 123 1 5/17/2013 6 123 0 6/25/2013 7 123 0
6/28/2013 8 456 0 8/2/2013 9 456 1 5/10/2013 10 456 0 5/18/2013 11 456 1
8/23/2013 12 456 1 9/7/2013
I want to only show rows where a changed occurred for each Customer ID (in
this case, row 3, 6, 9, 10, 11 from above)
Desired Query Output
Customer ID Status Date 123 1 4/13/2013 123 0 6/25/2013 456 1 5/10/2013
456 0 5/18/2013 456 1 8/23/2013

No comments:

Post a Comment