Combine records to calculate - David S.

I have a table that contains arrival times for different vehicles. I would
like to be able to combine records by incident and calculate the difference
in time of thier arrival.

Example

incd    vehi    time
123    m03    12:04
123    e05    12:07
124    m02    14:09
124    e07    14:06

This is what I want to do.

incd    vehi1    vehi2    time    diff
123    m03    e05        12:04    :03
124    m02    e07        14:09    -:03

Notice that I want to always use the "m" vehicles as the primary and
calculate the difference in time of that even if it results in a negative
time, meaning they were not the first to arrive.

Can anyone help me?

ds

Combine records to calculate - Tom Cooper

Assuming that your time columns are of type datetime, then something like

Select incd, m.vehi as vehi1, e.vehi as vehi2, m.time, DateDiff(mi, m.time,
e.time) As diff
From yourtable m
Inner Join yourtable e On m.incd = e.incd
Where m.vehi Like 'm%' And e.vehi Like 'e%'

Tom

Combine records to calculate - Roy Goldhammer

Here is another example to solve it:
CREATE TABLE #tmp(id int,
Orig varchar(3),
Arrival datetime)


insert #tmp values(123,'m03','12:04')
insert #tmp values(123,'e05','12:07')
insert #tmp values(124,'m02','14:09')
insert #tmp values(124,'e07','14:06')


select ID, max(case when left(Orig,1) = 'm' then Orig else '' end) vehi1
, max(case when left(Orig,1) = 'e' then Orig else '' end) vehi2
, max(case when left(Orig,1) = 'm' then Arrival else '' end) time
, datediff(minute, max(case when left(Orig,1) = 'm' then Arrival else ''
end),
max(case when left(Orig,1) = 'e' then Arrival else '' end)) diff
from #tmp
GROUP BY ID