CODEWITHSUNDEEP
sqldatetime
SteewDK
SteewDK

Reputation: 363

SQL 2008 offset rows

I have a select statement like this

select t.time, t.temperature, t.wind
from dbo.weatherdata t

I want to add a offset temperature column, such that :

select t.time, t.temperature, t.temperature(t.time-1/24.0) as temperaturem1, t.wind
from dbo.weatherdata t

As of 2012 I can see the offset function may be usable for this purpose. However, how would you recommend to solve this problem in SQL 2008R2?

I may add that the statement should be done in the least computational heavy method possible. This is due to the fact that dbo.weatherdata is large. Hence, if I would be able to avoid joins that would be preferable.

Thanks

Upvotes: 0

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

If you have a reading every hour, you can use the lag() function:

select t.time, t.temperature,
       lag(t.temperature) over (order by temperature) as temperaturem1,
       t.wind
from dbo.weatherdata t;

This pulls the temperature from the previous row, where previous is defined by the order by clause.

EDIT:

If you have irregular readings or for SQL Server 2008, then I think you need to use a join. The natural way to write this would be:

select t.time, t.temperature, tprev.temperature as temperaturem1,
       t.wind
from dbo.weatherdata t left outer join
     dob.weatherdata tprev
     on tprev.time = t.time - 1/24.0;

However, this probably would not work, because the floating point arithmetic is not exact. You could try something like:

select t.time, t.temperature, tprev.temperature as temperaturem1,
       t.wind
from dbo.weatherdata t left outer join
     dob.weatherdata tprev
     on tprev.time > t.time - (1/24.0 + 0.001) and
        tprev.time < t.time + (1/24.0 + 0.001) ;

Actually, I think I like cross apply better:

select t.time, t.temperature, tprev.temperature as temperaturem1,
       t.wind
from dbo.weatherdata t cross apply
     (select top 1 tprev.*
      dob.weatherdata tprev
      order by abs(datediff(second, tprev.time, dateadd(second, -3600, t.time)))
     ) tprev;

Upvotes: 1

Related Questions