GROUP BY with date ranges when values change but then change back

by user1601118   Last Updated October 19, 2019 00:06 AM - source

Consider a historical table where a value was changed and then changed back:

value | date
  3   | 1/1/2019
  3   | 1/2/2019
  3   | 1/3/2019
  4   | 1/4/2019
  4   | 1/5/2019
  3   | 1/6/2019
  3   | 1/7/2019
  3   | 1/8/2019
  3   | 1/9/2019
  2   | 1/10/2019
  2   | 1/11/2019
  3   | 1/12/2019
  3   | 1/13/2019
  3   | 1/14/2019

How do I write a query that shows min and max dates that still shows the value change? I'm looking for:

value | min(date) | max(date)  | days_at_this_val
  3   | 1/1/2019  | 1/3/2019   |        3
  4   | 1/4/2019  | 1/5/2019   |        2
  3   | 1/6/2019  | 1/9/2019   |        4
  2   | 1/10/2019 | 1/11/2019  |        2
  3   | 1/12/2019 | 1/14/2019  |        3

GROUP BY obviously lumps all the 3s together, and RANK and ROW_NUMBER resume counting 3s where they left off. What's the best way to do this?



Related Questions