Calculate Time Difference Between Two Rows
database, difference-between-rows, sql-server
Solution
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
Problem
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.