SQL Server – Finding Rows Where Data Resets

sql serversql-server-2012

So I have a table tbl where I'm trying to pull data that increments at a variable rate. I need to identify when the data resets so I can make a cumulative value. Here's an example table:

ID    VAL
 1     12
 2      2
 3     14
 4     20
 5     24
 6     25
 7      2
 8     11
 9     14
10     15
11     18
12      0
13      5

How do I identify in a SQL query which rows have reset data?

In this table example, I'd want to identify row IDs 2, 7, and 12 as the points where the data resets.

The end result is so I can add rows 1, 6, and 11 (with other data) to the current value for a total cumulative value.

Best Answer

;WITH x AS 
(
  SELECT ID, VAL, Prev_VAL = LAG(VAL, 1) OVER (ORDER BY ID) 
  FROM dbo.your_table
)
SELECT ID, VAL, Prev_VAL, Cumulative = VAL + Prev_VAL
FROM x 
WHERE VAL < Prev_VAL
ORDER BY ID;

If you need to support versions older than SQL Server 2012 (when LAG() was introduced), you can do this, but by casual and hardly-scientific observation, it is about 4X as expensive:

;WITH x AS 
(
  SELECT ID, VAL, Prev_ID = ROW_NUMBER() OVER (ORDER BY ID)
  FROM dbo.your_table
)
SELECT c.ID, c.VAL, p.VAL, Cumulative = c.VAL + p.VAL
FROM x AS c
LEFT OUTER JOIN x AS p
ON c.Prev_ID = p.Prev_ID + 1
WHERE c.VAL < p.VAL
ORDER BY c.ID;
Related Question