Better way to summarize data about stop times?
- by Vimvq1987
This question is close to this:
http://stackoverflow.com/questions/2947963/find-the-period-of-over-speed
Here's my table:
Longtitude Latitude Velocity Time
102 401 40 2010-06-01 10:22:34.000
103 403 50 2010-06-01 10:40:00.000
104 405 0 2010-06-01 11:00:03.000
104 405 0 2010-06-01 11:10:05.000
105 406 35 2010-06-01 11:15:30.000
106 403 60 2010-06-01 11:20:00.000
108 404 70 2010-06-01 11:30:05.000
109 405 0 2010-06-01 11:35:00.000
109 405 0 2010-06-01 11:40:00.000
105 407 40 2010-06-01 11:50:00.000
104 406 30 2010-06-01 12:00:00.000
101 409 50 2010-06-01 12:05:30.000
104 405 0 2010-06-01 11:05:30.000
I want to summarize times when vehicle had stopped (velocity = 0), include: it had stopped since "when" to "when" in how much minutes, how many times it stopped and how much time it stopped.
I wrote this query to do it:
select longtitude, latitude, MIN(time), MAX(time), DATEDIFF(minute, MIN(Time), MAX(time))
as Timespan from table_1 where velocity = 0 group by longtitude,latitude
select DATEDIFF(minute, MIN(Time), MAX(time)) as minute into #temp3
from table_1 where velocity = 0 group by longtitude,latitude
select COUNT(*) as [number]from #temp
select SUM(minute) as [totaltime] from #temp3
drop table #temp
This query return:
longtitude latitude (No column name) (No column name) Timespan
104 405 2010-06-01 11:00:03.000 2010-06-01 11:10:05.000 10
109 405 2010-06-01 11:35:00.000 2010-06-01 11:40:00.000 5
number
2
totaltime
15
You can see, it works fine, but I really don't like the #temp table. Is there anyway to query this without use a temp table?
Thank you.
