4

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.

  • 1
    You mean that the value "resets" if it's less than the immediate previous value? You can use LAG() function for that. – ypercubeᵀᴹ Jun 30 '15 at 17:38
7
;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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.