Calculate Time Difference Between Two Rows

Sql ServerDatabaseDifference Between-Rows

Sql Server Problem Overview


I have a table that contains the following:

DataDate                 Value
2010-03-01 08:31:32.000  100
2010-03-01 08:31:40.000  110
2010-03-01 08:31:42.000  95
2010-03-01 08:31:45.000  101
.                        .
.                        .
.                        .

I need to multiply the value column by the difference in time between the current and previous rows and sum that for the entire day.

I currently have the data set up to come in every 10 seconds which makes for a simple conversion in the query:

SELECT Sum((Value/6) FROM History WHERE DataDate BETWEEN @startDate and @endDate

Where @startDate and @endDate are today's date at 00:00:00 and 11:59:59.

Before I set the data to be collected every 10 seconds it was collected whenever the Value changed. There aren't any duplicate entries in terms of time, the minimum time difference is 1 second.

How can I set up a query to get the elapsed time between rows for the case when I don't know the time interval between readings?

I am using SQL Server 2005.

Sql Server Solutions


Solution 1 - Sql Server

WITH    rows AS
        (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY DataDate) AS rn
        FROM    mytable
        )
SELECT  DATEDIFF(second, mc.DataDate, mp.DataDate)
FROM    rows mc
JOIN    rows mp
ON      mc.rn = mp.rn - 1

In SQL Server 2012+:

SELECT  DATEDIFF(second, pDataDate, dataDate)
FROM    (
        SELECT  *,
                LAG(dataDate) OVER (ORDER BY dataDate) pDataDate
        FROM    rows
        ) q
WHERE   pDataDate IS NOT NULL

Solution 2 - Sql Server

A little tweak on Quassnoi's query if you prefer not to use a Subselect would be:

SELECT
      DATEDIFF(second, LAG(dataDate) OVER (ORDER BY dataDate), dataDate)
FROM  rows
WHERE LAG(dataDate) OVER (ORDER BY dataDate) IS NOT NULL

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionamarcyView Question on Stackoverflow
Solution 1 - Sql ServerQuassnoiView Answer on Stackoverflow
Solution 2 - Sql ServerHelvioView Answer on Stackoverflow