Hi everyone,

I have a SQL table like this

  Events         time                    endTime
  Tram          2014-11-28 12:35:50.390  2014-11-28 12:43:19.120
  Re-Entry      2014-11-28 12:43:19.120  2014-11-28 12:56:07.040
  Tram          2014-11-28 12:56:07.040  2014-11-28 13:15:25.060  // EndDate Before dump
  Dump          2014-11-28 13:15:25.060  2014-11-28 13:50:07.233
  Tram          2014-11-28 13:50:07.233  2014-11-28 13:55:17.473
  Load          2014-11-28 13:55:17.473  2014-11-28 14:06:55.063
  Tram          2014-11-28 14:06:55.063  2014-11-28 14:37:12.100
  Dump          2014-11-28 14:37:12.100  2014-11-28 14:37:12.100

I want to calculate the Difference between 2 dates like endtime before Dump - time

I am expecting output like this

Row1 Row2

1 00:39:34//2014-11-28 13:15:25.060-2014-11-28 12:35:50.390 2 23:12:55//2014-11-28 13:50:07.233-2014-11-28 14:37:12.100


You can find the details in SQL Fiddle he

create table #t (events varchar(15), dt datetime)

insert into #t values ('Tram','2014-11-28 12:35:50.390')
insert into #t values ('Re-Entry','2014-11-28 12:43:19.120')
insert into #t values ('Tram','2014-11-28 12:56:07.040')
insert into #t values ('Dump','2014-11-28 13:15:25.060')

insert into #t values ('Tram','2014-11-28 13:50:07.233')
insert into #t values ('Load','2014-11-28 13:55:17.473')
insert into #t values ('Tram','2014-11-28 14:06:55.063')
insert into #t values ('Dump','2014-11-28 14:37:12.100')


with cte
as
(
select *, row_number() over (order by dt) rn 
 from #t
),cte1
as
( select min(case when events ='Dump' then rn end) minpos,
         max(case when events ='Dump' then rn end) maxpos
from cte
) select datediff(minute,st,endt),st,endt
from
( 
select  (select top 1 dt from cte t where t.rn<cte1.minpos) st,
          (select top 1 dt from cte t where t.events='Dump' order by dt asc) endt
           
 from cte1
 union all
 select (select top 1 dt from cte t where t.rn>cte1.minpos and t.rn<cte1.maxpos),
        (select top 1 dt from cte t where t.events='Dump' order by dt desc)
from cte1
) as der