Consider some table T
, ordered by Col1, Col2, Date1, Date2
:
Col1 Col2 Date1 Date2 rate
ABC 123 11/4/2014 11/5/2014 -90
ABC 123 11/4/2014 11/6/2014 -55
ABC 123 11/4/2014 11/7/2014 -90
ABC 123 11/4/2014 11/10/2014 -90
I want to group the data so that changes are easily audited/reduce repetition, so I have
Col1 Col2 Date1 start_Date2 end_Date2 rate
ABC 123 11/4/2014 11/5/2014 11/5/2014 -90
ABC 123 11/4/2014 11/6/2014 11/6/2014 -55
ABC 123 11/4/2014 11/7/2014 11/10/2014 -90
I can easily do that if I can get another column with the rows numbered as 1 2 3 3
(only important that numbers are distinct), and then GROUP BY
that column.
My attempt at the query:
SELECT *, DENSE_RANK() OVER (ORDER BY rate) island
FROM T
ORDER BY Date2
doesn't give what I'm looking for:
Col1 Col2 Date1 Date2 rate island
ABC 123 11/4/2014 11/5/2014 -90 1
ABC 123 11/4/2014 11/6/2014 -55 2
ABC 123 11/4/2014 11/7/2014 -90 1
ABC 123 11/4/2014 11/10/2014 -90 1
I want the query to recognize the second group of -90
values should be treated as a new group, since they appeared after a group with a different rate
.
The [gaps-and-islands] SQL tag was pretty helpful, but I'm not quite able to figure out how to handle when the rate reverts back to a previous value. How should I modify my query?