SQL Server banding timepoints based on value

Question

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

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).

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

So you're looking for groups of `value` which are the same, but only as they appear in continuous blocks with regard to increasing time? If so, then this is a gaps and islands problems of sorts. — Tim Biegeleisen, Apr 26 '17 at 10:17
You should show sample data which makes this requirement clear. Your current data leads to a simple `GROUP BY` query, which isn't what you want. — Tim Biegeleisen, Apr 26 '17 at 10:19
exactly - you can see that in the output row 3 and 6 have the same value but occurred within a different time banding. In short, I am trying to convert this discrete data stream into a more contiguous structure to aid querying — Mark Ruse, Apr 26 '17 at 10:19
Edited the question to hopefully make the requirement clearer — Mark Ruse, Apr 26 '17 at 10:24

score 2 · Accepted Answer · answered Apr 26 '17 at 10:58

2

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

answered Apr 26 '17 at 10:58

ridwanrohman95

34
3

Excellent - this is exactly what I was after and works perfectly - thanks – Mark Ruse Apr 26 '17 at 13:40
This is a really great answer, something to keep in mind. Here is a [link to a demo](http://rextester.com/ZQLAR86014) showing the query in action. It works. – Tim Biegeleisen Apr 27 '17 at 03:59

Keith · Answer 2 · 2017-04-26T10:23:22.900

1

After seeing your further comments, the below is obviously of no use...

I think you may be over complicating it. If you are looking for the minimum and maximum ts per distinct value, then you can just use MIN & MAX and group by the Value, e.g.

SELECT   MIN(ts) AS [Start]  ,
         MAX(ts) AS [End]    , 
         Value
FROM     Table
GROUP BY Value

edited Apr 26 '17 at 10:23

answered Apr 26 '17 at 10:11

Keith

1,008
10
19

SQL Server banding timepoints based on value

2 Answers2