Given a data set like this:
…Rows excluded for brevity…
Is it possible to write a query to return a result set where each row is a contiguous date range, grouped by the id column? Like this:
|Id||Start Date||End Date||Sum of Value|
I’m imagining it will be some combination of window functions and group bys, but I’m not even sure it’s possible and didn’t want to fumble towards a solution that didn’t exist.
I’m using SQL Server 2017 and 2019, if that matters.
EDIT – I should have included that the date ranges can be long – months or years. For example, one of the collapsed rows could be a year long, coming from 365 individual rows. I just didn’t want to paste an example data set that was a thousand lines long.