Given a data set like this:
|...||... (rows omitted 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.
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, like in the given example.