CODEWITHSUNDEEP
sql-servert-sql
Mason Younger
Mason Younger

Reputation: 63

Difference between consecutive rows in table

I need help calculating the difference between each row of data. The count increments and data is captured every hour.

+-------------------------+------------------+
|         t_stamp         |  qty             |
+-------------------------+------------------+
| 2020-01-08 19:00:00.007 |           514096 |
| 2020-01-08 20:00:00.007 |           514096 |
| 2020-01-08 21:00:00.003 |           514096 |
| 2020-01-08 22:00:00.003 |           514096 |
| 2020-01-08 23:00:00.013 |           514096 |
| 2020-01-09 01:00:00.003 |           531181 |
| 2020-01-09 02:00:00.003 |           531181 |
| 2020-01-09 03:00:00.000 |           531181 |
+-------------------------+------------------+

What I would like to return is:


+-------------------------+------------------+
|         t_stamp         | count_per_hr     |
+-------------------------+------------------+
| 2020-01-08 19:00:00.007 |                0 |
| 2020-01-08 20:00:00.007 |                0 |
| 2020-01-08 21:00:00.003 |                0 |
| 2020-01-08 22:00:00.003 |                0 |
| 2020-01-08 23:00:00.013 |                0 |
| 2020-01-09 01:00:00.003 |            17085 |
| 2020-01-09 02:00:00.003 |                0 |
| 2020-01-09 03:00:00.000 |                0 |
+-------------------------+------------------+

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use lag():

select t.*,
       (qty - lag(qty, 1, qty) over (order by t_stamp)) as diff
from t;

Note that this uses the 3-argument form of lag() to convert missing previous values (i.e. the first value) to a specified value.

Upvotes: 1

Related Questions