CODEWITHSUNDEEP
sqlsql-server
Alex B
Alex B

Reputation: 23

Date Diff Between Rows in SQL

So I have this table with rows as listed:

IDCODE DATETIME DIFFERENCE (MINS)

1      02/03/2011 08:00-----10

2        02/03/2011 08:10-----13

3      02/03/2011 08:23 ----- 2

4       02/03/2011 08:25 -----  60

5       02/03/2011 09:25  -----      44

6       02/03/2011 10:20    -----    14

7       02/03/2011 10:34

I want to calculate the (next row - the current row's difference in time). So in row 1, the date from row 2 minus the date from row 1 is a 10 minute difference, so it will appear in row 1 as 10 as the difference in minutes.

Any help would be appreciated

Upvotes: 2

Views: 1759

Answers (4)

Prabhath Amaradasa
Prabhath Amaradasa

Reputation: 503

Another solution using CTE 

WITH DateCte AS
(
 SELECT 
  idcode,
  mydatetime,
  RANK() OVER ( ORDER BY idcode) AS CurrentRank,
  (RANK() OVER ( ORDER BY idcode) + 1 )AS NextRank  
  FROM Table1
 ) 

 SELECT
   ce.idcode,
   DATEDIFF (minute, ce.mydatetime, ne.mydatetime) AS DateDifference
 FROM DateCte AS ce LEFT OUTER JOIN  DateCte ne ON ce.NextRank = ne.CurrentRank

Sample Code

Upvotes: 1

KeithL
KeithL

Reputation: 5594

I used the older version of the self join to solve your problem:

declare @t table (IDCODE int , dt datetime)

insert into @t
values
(1      ,'02/03/2011 08:00')-----10    
,(2        ,'02/03/2011 08:10')-----13    
,(3      ,'02/03/2011 08:23') ----- 2    
,(4       ,'02/03/2011 08:25') -----  60    
,(5       ,'02/03/2011 09:25')  -----      44    
,(6       ,'02/03/2011 10:20')    -----    14    
,(7       ,'02/03/2011 10:34')

;with cte as
    (
    select *,rn=ROW_NUMBER() over (order by IDCODE)
    from @t
    )

select base.IDCODE
      , base.dt
      , case when future.IDCODE is null 
           then null 
           else datediff(minute,base.dt,future.dt) 
        end as DIFFERENCE
from cte as base
left join cte as future on base.rn+1 = future.rn

Results:

IDCODE  dt  DIFFERENCE
1   2011-02-03 08:00:00.000 10
2   2011-02-03 08:10:00.000 13
3   2011-02-03 08:23:00.000 2
4   2011-02-03 08:25:00.000 60
5   2011-02-03 09:25:00.000 55
6   2011-02-03 10:20:00.000 14
7   2011-02-03 10:34:00.000 NULL

Upvotes: 1

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

Demo on Lead and CTE using row_number

This solution is not dependent on the IDCODE as an identifier but as the sequence, if IDCODE is irrelevant then Lead and Row_number order by mydatetime

declare @mytable table (idcode int, mydatetime datetime)

insert into @mytable
values
(1      ,'02/03/2011 08:00'),-----10
(2        ,'02/03/2011 08:10'),-----13
(3      ,'02/03/2011 08:23'), ----- 2
(4       ,'02/03/2011 08:25'), -----  60
(5       ,'02/03/2011 09:25'),  -----      44
(6       ,'02/03/2011 10:20'),    -----    14
(7       ,'02/03/2011 10:34')



-- using Lead
select
mydatetime,
lead(mydatetime,1,null) over (order by idcode) [mynextdatetime],
datediff(minute,mydatetime,lead(mydatetime,1,null) over (order by idcode)) [DatediffMinute]
from @mytable


-- using CTE

;with myDateList as (
  select row_number() over (order by idcode) rn,
  idcode,
  mydatetime
  from @mytable
 ) ,
myNextDate as
(
select  
  row_number() over (order by idcode) nextrn,
  idcode,
  mydatetime [nextdate]
  from
  myDateList
  where rn > 1
)
 select d.idcode,d.mydatetime, n.nextdate,
 datediff(minute,d.mydatetime,n.nextdate) datediffbyminutes
  from myDateList D
 left outer join myNextDate N
   on d.rn = n.nextrn 
 ;

Upvotes: 2

Conrad S.
Conrad S.

Reputation: 884

Join the table to itself, and in the On section of the join, you can specify something like "ON MyTable.ID = MyTable.ID -1".

This depends on the IDs being sequential with no gaps.

Upvotes: 1

Related Questions