61

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.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
amarcy
  • 1,485
  • 2
  • 19
  • 28

2 Answers2

175
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
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • Also remember to rather LEFT JOIN on the rows and change to ISNULL(DATEDIFF(second, mc.DataDate, mp.DataDate),'00:00:000') time, for that first record. – Stonck Apr 01 '15 at 10:26
7

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
Helvio
  • 707
  • 1
  • 7
  • 15
  • How could this be modified if I have a row for starting and stopping a job? I have one row for starting a job and another row with stopping a job with times. – M Akin Dec 03 '18 at 14:55