Menu
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.
992
To calculate a difference, you need a pair of records; those two records are “the current record” and “the previous year's record”. You obtain this record using the LAG() window function. This function allows you to obtain data from the previous record (based on an order criterion, which here is “ ORDER BY year ”).
Discussion: If you'd like to calculate the difference between the timestamps in seconds, multiply the decimal difference in days by the number of seconds in a day, which equals 24 * 60 * 60 = 86400 , or the product of the number of hours in a day, the number of minutes in an hour, and the number of seconds in a minute.
To calculate the difference between the arrival and the departure in T-SQL, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument can be microsecond , second , minute , hour , day , week , month , quarter , or year .
Example 1: Comparing rows of the same table. In the example, we are comparing the immediate rows to calculate the sales made on a day by comparing the amounts of two consecutive days. Syntax for inner join : SELECT column_name(s) FROM table1 t1 INNER JOIN table1 t2 on t1. column1 = t2.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
