CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Zelong
Zelong

Reputation: 2556

How to subtract two consecutive rows in MS SQL Server?

A table looks like:

id    | location | datetime
------| ---------| --------
CD123 | loc001   | 2010-10-21 13:30:15
ZY123 | loc001   | 2010-10-21 13:40:15
YU333 | loc001   | 2010-10-21 13:41:00
AB456 | loc002   | 2011-1-21 14:30:30
FG121 | loc002   | 2011-1-21 14:31:00
BN010 | loc002   | 2011-1-21 14:32:00

Assume the table has been sorted by ascending datetime. I am trying to find the elapse (in seconds) between two consecutive rows within a location.

The result table is supposed to be:

| location | elapse 
| loc001   | 600  
| loc001   | 45
| loc002   | 30
| loc002   | 60

Since the id is randomly generated, it is difficult to write something like a.id = b.id + 1 in a query. And only rows within the same location is consecutively subtracted, not across different locations.

How should I write a query in MS SQL Server to accomplish it?

Upvotes: 2

Views: 4884

Answers (5)

Dudi Konfino
Dudi Konfino

Reputation: 1136

Create cte and use lead to get datetime and next_datetime at same row. Then calculate with datediff using this cte 

WITH cte
    AS
    (
        SELECT location
        ,      datetime
        ,      lead(datetime,1) OVER (patition BY location ORDER BY datetime asc) next_datetime
        from tbl)
    SELECT location
    ,      datediff(ss,next_datetime,datetime) Elepase 
    FROM cte

Upvotes: 0

mordechai
mordechai

Reputation: 851

In SQL Server 2012 and later you can use LEAD or LAG

SELECT 
   location,
   SUM(DATEDIFF(SECOND, DateTime, 
                Lead(DateTime, 1) OVER(PARTITION BY location ORDER BY DateTime))) Elepase 
FROM 
    tableName  
GROUP BY 
    location

Upvotes: 4

mucio
mucio

Reputation: 7119

You can try this way:

  select s.location,
         s.datetime,
         datediff(ss, s.datetime, s.prev_datetime)
    from (
           select location, 
                  datetime,
                  lead(datetime) over (partition by location order by datetime ) prev_datetime
             from Table1 
         ) s
   where s.prev_datetime is not null
order by s.location, 
         s.datetime desc

Upvotes: 0

Mohsen
Mohsen

Reputation: 79

with Result as 
(Select *, ROW_NUMBER() Over (order by location,datetime) RowID from table_name  )
Select R1.location,DATEDIFF(SECOND,R2.datetime,R1.datetime)   from Result R1     Inner join Result R2 on (R1.RowID=R2.RowID+1 and R1.location=r2.location)

Upvotes: 2

sarin
sarin

Reputation: 5307

You have two options:

Add a new Row number column and then self join this on the ID e.g. [NEW ID] = [NEW ID] - 1. You can then do the subtraction i.e. Table1.[New ID] - Table2.[New ID]

Use the LAG function which is a shortcut for the above method. As long as you are using SQL2012+

Upvotes: 0

Related Questions