Calculate Time Difference of two consecutive rows in one column of a table in SQL Server -

i have query

table1 ____________________________________________________________________ uniqueid   ticketnumber   action                    date --------------------------------------------------------------------  1         123456         dependency occured        3/25/2015 7:40:39  2         123456         tech support requested    3/25/2015 10:00:47  3         123456         tech support given        3/25/2015 11:30:40  4         123456         dependency occured        3/25/2015 02:30:40 pm  5         123456         tech support given        3/25/2015 03:30:40 pm 

here same ticketnumber there various actions performed @ given time. have find total time 'dependency occured'. date of action, dependency occurred has subtracted row below it, have calculate total time dependency has occurred. 1st row - 2nd row gives 2 hrs 20 mins. , 4th row - 5th row gives 1 hour. total dependency occurred 3 hrs 20 mins.

thanks in advance.

assuming use sql server 2012 or higher, can use lead solve this:

;with cte1  ( select  ticketnumber,          action,          date,         lead(date) over(partition ticketnumber order date) nextactiondate table1 ), cte2 ( select ticketnumber,         sum(datediff(minute, date, nextactiondate)) timeofdependencyoccured cte1  action = 'dependency occured' group ticketnumber )  select ticketnumber,         cast(timeofdependencyoccured / 60 varchar(2)) + ':' +         cast(timeofdependencyoccured % 60 varchar(2)) cte2 

see fiddle here

for 2005 / 2008 have use sub query instead of lead:

;with cte1  ( select  ticketnumber,          action,          date,         (select top 1 date           table1 t2          t2.date > t1.date          , t2.ticketnumber = t1.ticketnumber          order date         ) nextactiondate table1 t1 ), cte2 ( select ticketnumber,         sum(datediff(minute, date, nextactiondate)) timeofdependencyoccured cte1  action = 'dependency occured' group ticketnumber )  select ticketnumber,         cast(timeofdependencyoccured / 60 varchar(2)) + ':' +         cast(timeofdependencyoccured % 60 varchar(2)) cte2 

see fiddle here