Calculating the Difference between row values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Calculating the Difference between row values

Author

Topic

asnyder
Starting Member

3 Posts

Posted - 2014-12-29 : 13:50:00

I've been searching for a way to calculate the difference between row values for the same field, but none of the formulas I have found have worked for me.

I have a field {table.read} that provides a reading in integer format.
I also have a datetime field {table.read_date} that tells me when the {table.read} field was captured.

What I need to do is calculate the difference between the {table.read} field and the previous {table.read} field for each row (but only for readings for the same deviceID).

The records are grouped by {table.DevID} and sorted {table.read_date} DESC.

So, for example, my data looks like:

DEVID / READ / READ_DT
1 / 500 / 12-20-2014
1 / 425 / 11-21-2014
1 / 300 / 10-19-2014
2 / 800 / 12-17-2014
2 / 745 / 11-16-2014
2 / 600 / 10-21-2014

I need to add a DIFF field to each row that subtracts the previous read from that rows read.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-29 : 14:21:00

use can use LAG/LEAD for that: http://msdn.microsoft.com/en-us/library/hh231256.aspx

e.g. (untested, but enough to give you the idea I think)


select <other columns>
     table.read - lag(table.read) over (partition by deviceid order by read_Dt) as diff
from ...

asnyder
Starting Member

3 Posts

Posted - 2014-12-30 : 11:23:19

Thanks for the response gbritton. I couldn't quite figure out how to use lag/lead to solve my issue, but after about 100 attempts at other solutions, I finally figured out something that worked.

I'm fairly new to SQL querying. Persistence is gold.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-30 : 11:49:23

e.g.


declare @t table (devid int, _read int, read_dt DATE)
insert into @t (devid, _read, read_dt) values
--DEVID / READ / READ_DT
(1, 500 , '12-20-2014'),
(1, 425 , '11-21-2014'),
(1, 300 , '10-19-2014'),
(2, 800 , '12-17-2014'),
(2, 745 , '11-16-2014'),
(2, 600 , '10-21-2014')

select DEVID, _read, read_dt,
     _read - lag(_read) over (partition by devid order by read_Dt) as diff
from @t

produces:


DEVID	_read	read_dt  	diff
1	300	2014-10-19	NULL
1	425	2014-11-21	125
1	500	2014-12-20	75
2	600	2014-10-21	NULL
2	745	2014-11-16	145
2	800	2014-12-17	55

asnyder
Starting Member

3 Posts

Posted - 2014-12-31 : 09:57:33

Much simpler solution that I came up with. Thanks so much gbritton!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources