CODEWITHSUNDEEP
sqlsql-servert-sql
GlenCloncurry
GlenCloncurry

Reputation: 517

Difference between two rows with date in ascending order

I am trying to write a query which calculates the difference between the value rows as a new column called difference when the datetime field is in ascending order.

For example, 2016-03-02 should be 102340624 - 102269208 

select datetime, tagname, value
from runtime.dbo.AnalogHistory
where datetime between '20160301 00:00' and '20160401 00:00'
and TagName = 'EWS_A3_PQM.3P_REAL_U'
and wwResolution = (1440 * 60000)
order by DateTime asc

DATETIME                    TAGNAME                 VALUE       DIFFERENCE
2016-03-01 00:00:00.0000000 EWS_A3_PQM.3P_REAL_U    102269208
2016-03-02 00:00:00.0000000 EWS_A3_PQM.3P_REAL_U    102340624
2016-03-03 00:00:00.0000000 EWS_A3_PQM.3P_REAL_U    102411568
2016-03-04 00:00:00.0000000 EWS_A3_PQM.3P_REAL_U    102478104
2016-03-05 00:00:00.0000000 EWS_A3_PQM.3P_REAL_U    102549088
2016-03-06 00:00:00.0000000 EWS_A3_PQM.3P_REAL_U    102612592
2016-03-07 00:00:00.0000000 EWS_A3_PQM.3P_REAL_U    102682984
2016-03-08 00:00:00.0000000 EWS_A3_PQM.3P_REAL_U    102747000
2016-03-09 00:00:00.0000000 EWS_A3_PQM.3P_REAL_U    102817176
2016-03-10 00:00:00.0000000 EWS_A3_PQM.3P_REAL_U    102887896

Thank you very much in advance

Upvotes: 3

Views: 519

Answers (3)

TheGameiswar
TheGameiswar

Reputation: 28900

This works for any version..All you need to do is add +1 to your date to get next column,for last column,you obviosuly wont have next value,you can use is null function to customize..

select 
 t1.*,isnull(b.difference ,t1.value) as difference  from yourtable t1
outer apply
(
select (t2.value-t1.value) as difference from yourtable t2 
where dateadd(day,1,t1.date)=t2.date
) b

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

For sql server versions 2005 or higher but before 2012 (where you don't have lag and lead functions)

;with cte as 
(
    select datetime, tagname, value
    from runtime.dbo.AnalogHistory
    where datetime between '20160301 00:00' and '20160401 00:00'
    and TagName = 'EWS_A3_PQM.3P_REAL_U'
    and wwResolution = (1440 * 60000)
)

select datetime, tagname, value, value - isnull((select top 1 value from cte t2 where t2.datetime < t1.datetime order by t2.datetime desc), 0) as difference
from cte t1 
order by DateTime

For sql server 2012 or higher:

select datetime, tagname, value, value - isnull(lag(value) over (order by datetime), 0)
from runtime.dbo.AnalogHistory
where datetime between '20160301 00:00' and '20160401 00:00'
and TagName = 'EWS_A3_PQM.3P_REAL_U'
and wwResolution = (1440 * 60000)
order by DateTime

Upvotes: 0

Wyatt Shipman
Wyatt Shipman

Reputation: 1789

You can use the lag function to get the previous rows value.

Select datetime, tagname, value, value- coalesce(lag(value) over(partition by tagname order by datetime),0) [difference]
from runtime.dbo.AnalogHistory
where datetime between '20160301 00:00' and '20160401 00:00'
and TagName = 'EWS_A3_PQM.3P_REAL_U'
and wwResolution = (1440 * 60000)
order by DateTime asc

Upvotes: 2

Related Questions