Search code examples
sql-serveraggregatelaglead

SQL Server banding timepoints based on value

I have audit data captured over time points which I would like to aggregate up to display where the value was the same over a period of time using SQL Server 2014.

Taking the below example data

enter image description here

I would like to transpose it to this ideally without using a cursor - you will see that the output is recording the time period where the value is the same and as such, the same value can be repeated over different periods (seen in rows 3 and 6).

enter image description here

I have been looking at LEAD() and LAG() as potential solutions, however I cannot fathom out how to make this work in order to band by time for the same value

Any direction would be gratefully received

Solution

  • In case the column [value] doesnt contain distinct number, you can use this query

     SELECT start,end,value 
 FROM (SELECT MIN(ts) start
             ,MAX(ts) end
             ,value
             ,C 
       FROM (SELECT ts
                   ,value
                   ,(ROW_NUMBER() OVER (ORDER BY ts) 
                    - ROW_NUMBER() OVER (PARTITION BY value ORDER BY ts)) C
             FROM YourTable) x
 GROUP BY value,C) y ORDER BY start