SQL Calculate Difference Between Current and Last Value By Timestamp Column

Question

I am looking to calculate the difference between the current & last Value organised by the timestamp column?

My table is organised as follows:

MeterID(PK,FK,int.not null), ActualTimeStamp(smalldatetime,not null), Value(float,null)

Meter ID     ActualTimeStamp     Value          
312514     2013-01-01 08:08:00   72         
312514     2013-01-01 08:07:00   12

So my answer should be 72 - 12 = 60

The only solutions I can seem to find are using Row Number which i dont have an option of, if anyone can assist id really apprecieate it as its busting my brain.

Which RDBMS are you using (eg MySQL, SQL-Server, PostgreSQL) — bendataclear, Feb 21 '13 at 10:16
I thought that I would have to have a row number column to organise by? My querys will be individual querys by meter id calculating the difference between current and last value. One option I was looking at was: SELECT [current].rowInt, [current].Value, ISNULL([next].Value, 0) - [current].Value FROM sourceTable AS [current] LEFT JOIN sourceTable AS [next] ON [next].rowInt = (SELECT MIN(rowInt) FROM sourceTable WHERE rowInt > [current].rowInt) — Aaron, Feb 21 '13 at 10:30
Can you provide more on your expected output, say Meter ID 312514 has 3 records? Do you want to get the answer as another column? — Lloyd Santos, Feb 21 '13 at 10:35
The meters recieve updated values every 30 mins, they are electricity meters and flow meters ect.. I am only looking to obtain the difference between the current value and previous value for each meter. There will be an individual query built for each meter and will poll SQL every 10mins for updates. — Aaron, Feb 21 '13 at 10:40

score 1 · Accepted Answer · answered Feb 21 '13 at 10:44

1

Here's a query that can help you. Just modify this to fit your need/table names/etc.

with sub as (
  select meterid,
         actualtimestamp,
         value,
         row_number() over (partition by meterid order by actualtimestamp desc) as rn
     from test  
  )
select meterid,
       actualtimestamp,
       value,
       value - isnull((select value
                 from sub
                where s.meterid = meterid
                  and rn = s.rn + 1), value) as answer
from sub s
order by meterid, actualtimestamp desc;

Basically what it does is that it adds a row number using the row_number() aggregate function. Using the row number, the query tries to get the value from the previous entry and getting the value difference.

Try the fiddler here

answered Feb 21 '13 at 10:44

Lloyd Santos

392
2
14

Thanks Lloyd this is exactly what im looking for. :) – Aaron Feb 21 '13 at 11:22
Hi Lloyd, I had the query working perfect in SQL and when i put it into the app im using it failed, upon further investigation into the logs it said: [Microsoft][ODBC SQL Server Driver]Incorrect syntax near keyword'by'. Is there a variation I can use with an ODBC Sql request? – Aaron Feb 21 '13 at 11:54
This is my exact query: with sub as (select meterid,actualtimestamp,value,row_number() over (partition by meterid order by actualtimestamp desc) as rn from esight.dbo.MeterData where ActualTimeStamp >=DATEADD(dd,DATEDIFF(dd,0,getdate()), 0) and MeterID = '31574')select value - isnull((select value from sub where s.meterid = meterid and rn = s.rn + 1), value) as answer,0 from sub sorder by meterid, actualtimestamp desc; – Aaron Feb 21 '13 at 12:08
The error is within the app so its quite difficult to export let me try get it for you. – Aaron Feb 21 '13 at 12:09
`from sub sorder` Make sure to have a space between the alias "s" and order. – Lloyd Santos Feb 21 '13 at 12:11
Point - Esight Test : No 'IF @variable' detected. E 21-Feb-13 11:39:10 RDBMSPI:RDBMSPI:ESIGHT Point - Esight Test : doTrialExec() : [S][42000]: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'by'. E 21-Feb-13 11:39:10 RDBMSPI:RDBMSPI:ESIGHT Point - Esight Test : doTrialExec() : [S][42000]: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. E 21-Feb-13 11:39:10 RDBMSPI:RDBMSPI:ESIGHT | 1 | >> Point - Esight Test : tag refused. I 21-Feb-13 11:42:45 RDBMSPI:RDBMSPI:ESIGHT | 1 | 0 (31104) >> DeviceStatus=0 – Aaron Feb 21 '13 at 12:13
Just found an operate in compatabillity mode in the interface and it solved the issue. Again many thanks for your help. ;) – Aaron Feb 21 '13 at 12:17

score 0 · Answer 2 · answered Mar 05 '17 at 05:22

In SQL Server 2008, I would recommend a outer applyhere the short code of find diff with your requirement

    select t.*, isnull((t.value - tprev.value),0) as diff
from test t outer apply
     (select top 1 tprev.*
      from test tprev
      where tprev.meterid = t.meterid and
            tprev.actualtimestamp < t.actualtimestamp
      order by tprev.actualtimestamp desc
       )tprev

SQL Calculate Difference Between Current and Last Value By Timestamp Column

2 Answers2