Hi,
I'm having difficulty constructing a query that will find breaks where
data change in a time-series. I've done some searching for this too, but
I haven't found anything.
Here is my example situation, consider my source table:
date bin
2009-01-01 red
2009-01-02 red
2009-01-03 blue
2009-01-04 blue
2009-01-05 blue
2009-01-06 red
2009-01-07 blue
2009-01-08 blue
2009-01-09 red
2009-01-10 red
I would like to get the first and last of each consecutive series based
on column "bin". My result for the table would look like:
first last bin
2009-01-01 2009-01-02 red
2009-01-03 2009-01-05 blue
2009-01-06 2009-01-06 red
2009-01-07 2009-01-08 blue
2009-01-09 2009-01-10 red
This is easy to compute using a spreadsheet or in R, but how would I do
this with SQL? I'm using 8.3. Advice is appreciated.
Thanks,
-Mike