Roy Goldhammer
3/29/2007 4:47:00 PM
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
"Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
news:uZDcbAicHHA.5044@TK2MSFTNGP05.phx.gbl...
> 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
>
> "David S." <davidstedman@colliergov.net> wrote in message
> news:OrHzC3hcHHA.4888@TK2MSFTNGP02.phx.gbl...
>>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
>>
>
>