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?
Best Answer
A common way of doing this is to compare the total order (regarding date) vs the relative order within each value, let's call the difference grp. If the grp changes, it means that date from another partition of value interfered with the current group. So by picking the min date for each value, grp we can achieve what you want. I'll leave days_at_this_val as an exercise for the reader;-)
DB<>fiddle
For future postings, you will get more (and thereby better) answers if you simplify for those trying to answer, by posting ddl and sql instead of ascii tables:
or by creating a fiddle like the one I provided in the link.