473,573 Members | 3,248 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Stop time duration problem

I am working with a proprietary database that records the date, time,
location, and speed of a vehicle. It is pulling this information from
GPS unit tied to a vehicle. The table is populated with values that
are pulled from the GPS unit every 30 seconds. I need to find the
duration of time for when a vehicle is stopped. I have created a
cursor that runs though all of the tables, and gathers the data for
when the vehicle's speed is equal to zero. I have inserted this data
into a temp table, where I am having a problem is identifying the
duration of time the vehicle is stopped. I cannot figure out how to
query the table and grab the first row when the speed is zero and the
last row where the speed is zero.

The data looks like this…

Date Time VehID Lat Long Speed
12/31/2003 66144 2957085352208 42.92747 -83.63003 0
12/31/2003 66159 2957085352208 42.92696 -83.62935 0
12/31/2003 66179 2957085352208 42.9271 -83.62902 0
12/31/2003 66269 2957085352208 42.92709 -83.62903 0
12/31/2003 66359 2957085352208 42.9271 -83.62901 0
12/31/2003 66449 2957085352208 42.92709 -83.62904 0
12/31/2003 66539 2957085352208 42.92708 -83.62903 0
12/31/2003 66629 2957085352208 42.92708 -83.629 0
12/31/2003 66719 2957085352208 42.92708 -83.62903 0
12/31/2003 67414 2957085352208 42.9269 -83.63092 0

Any help would be greatly appreciated…

Thanks,

Dave
Jul 20 '05 #1
5 1854

"Dave" <Br***********@ gmail.com> wrote in message
news:df******** *************** ***@posting.goo gle.com...
I am working with a proprietary database that records the date, time,
location, and speed of a vehicle. It is pulling this information from
GPS unit tied to a vehicle. The table is populated with values that
are pulled from the GPS unit every 30 seconds. I need to find the
duration of time for when a vehicle is stopped. I have created a
cursor that runs though all of the tables, and gathers the data for
when the vehicle's speed is equal to zero. I have inserted this data
into a temp table, where I am having a problem is identifying the
duration of time the vehicle is stopped. I cannot figure out how to
query the table and grab the first row when the speed is zero and the
last row where the speed is zero.

The data looks like this.

Date Time VehID Lat Long Speed
12/31/2003 66144 2957085352208 42.92747 -83.63003 0
12/31/2003 66159 2957085352208 42.92696 -83.62935 0
12/31/2003 66179 2957085352208 42.9271 -83.62902 0
12/31/2003 66269 2957085352208 42.92709 -83.62903 0
12/31/2003 66359 2957085352208 42.9271 -83.62901 0
12/31/2003 66449 2957085352208 42.92709 -83.62904 0
12/31/2003 66539 2957085352208 42.92708 -83.62903 0
12/31/2003 66629 2957085352208 42.92708 -83.629 0
12/31/2003 66719 2957085352208 42.92708 -83.62903 0
12/31/2003 67414 2957085352208 42.9269 -83.63092 0

Any help would be greatly appreciated.

Thanks,

Dave


Here's one possibility - it's always best if you can post CREATE and INSERT
statements for your test case, otherwise people have to guess about data
types, constraints etc. I assumed that by "first" and "last" you meant the
minimum and maximum times for the same day - you might want to consider
putting the date and time together in a single datetime column, which would
make it a lot easier to work with the data (eg. with date/time functions),
but I appreciate that you might have no control over this.

Simon

create table dave (
ddate datetime not null, -- note that date and time are reserved keywords
dtime int not null,
vehid bigint not null,
lat dec(7,5) not null,
long dec(7,5) not null,
speed int not null,
constraint PK_dave primary key (vehid, ddate, dtime) -- this is a guess
)go

insert into dave
select '12/31/2003', 66144, 2957085352208, 42.92747, -83.63003 ,0
union all
select '12/31/2003', 66159, 2957085352208, 42.92696, -83.62935 ,0
union all
select '12/31/2003', 66179, 2957085352208, 42.9271 , -83.62902 ,
0
union all
select '12/31/2003', 66269, 2957085352208, 42.92709, -83.62903 ,0
union all
select '12/31/2003', 66359, 2957085352208, 42.9271 , -83.62901 ,0
union all
select '12/31/2003', 66449, 2957085352208, 42.92709, -83.62904 ,0
union all
select '12/31/2003', 66539, 2957085352208, 42.92708, -83.62903 ,0
union all
select '12/31/2003', 66629, 2957085352208, 42.92708, -83.629 ,0
union all
select '12/31/2003', 66719, 2957085352208, 42.92708, -83.62903 ,0
union all
select '12/31/2003', 67414, 2957085352208, 42.9269 , -83.63092 ,0
go

select d.*
from dave d
join (
select vehid, ddate, min(dtime) as 'mintime', max(dtime) as 'maxtime'
from dave
where speed = 0
group by vehid, ddate ) dt
on d.vehid = dt.vehid
and d.ddate = dt.ddate
and (d.dtime = dt.mintime or d.dtime = dt.maxtime)
go

drop table dave
go
Jul 20 '05 #2


Simon,

Thanks for the information. I'll try and provide a little more
information as to what I am looking for.

MessageDate - Datetime field
Messagetime - Integer (this is based on the GMT 82000 second day)
Vehicle ID - varchar
Latitude - Float
Longitude - Float
Speed - Integer

All of the fields will not allow null values.

What is created from the developer is an individual table for each day
Position_01_01_ 03.

The cursor that I am using goes through each of the tables and grabs the
any speed values that are equal to 0, and moves them into temp table. It
also concatinates and Converts the MessageDate and Message Time into a
standard date/time value (e.g. 12/31/2003 666159 to 12/31/2003 6:22:59
PM).

The problem with the Min/Max function that I have run into is there can
be multiple stops for a vehicle in a single day. I need to identify each
of these stops independant of each other.
Again Thanks for the help!

Thanks,

Dave

Dave

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #3
Dave <Br***********@ gmail.com> wrote in message news:<41******* *************** @news.newsgroup s.ws>...
Simon,

Thanks for the information. I'll try and provide a little more
information as to what I am looking for.

MessageDate - Datetime field
Messagetime - Integer (this is based on the GMT 82000 second day)
Vehicle ID - varchar
Latitude - Float
Longitude - Float
Speed - Integer

All of the fields will not allow null values.

What is created from the developer is an individual table for each day
Position_01_01_ 03.

The cursor that I am using goes through each of the tables and grabs the
any speed values that are equal to 0, and moves them into temp table. It
also concatinates and Converts the MessageDate and Message Time into a
standard date/time value (e.g. 12/31/2003 666159 to 12/31/2003 6:22:59
PM).

The problem with the Min/Max function that I have run into is there can
be multiple stops for a vehicle in a single day. I need to identify each
of these stops independant of each other.
Again Thanks for the help!

Thanks,

Dave

Dave

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Can you not do this work in your cursor? Pseudocode follows:

declare @StartTime int
declare @LastTime int
declare @CurrentDate datetime
declare @CurrentState int -- 0 = Stationary, 1 = Moving
declare boris cursor for select <blah blah blah>

set @CurrentState = 1 --Assume we start moving, so that if first speed
is zero, we start a "stop"

open boris
fetch next from boris into <temporary variables>
while @@FETCH_STATUS = 0
begin
if <speed variable> = 0
begin
if @CurrentState = 1
begin
set @StartTime = <time variable>
set @CurrentDate = <date variable>
set @CurrentState = 0
end
set @LastTime = <time variable>
end

if <speed variable> <> 0 and @CurrentState = 0
begin
insert into <temp table> (<column list>) values
(@CurrentDate,@ LastTime - @StartTime,<Oth er fields>)
set @CurrentState = 1
end

fetch next from boris into <temp variables>
end
close boris
deallocate boris

if @CurrentState = 0
begin
insert into <temp table> (<column list>) values
(@CurrentDate,@ LastTime - @StartTime,<Oth er fields>)
end

Anyone see a problem with this?
Jul 20 '05 #4

I just wanted to thank everyone for the help...

Here is a look at the final cursor...

declare ptbl_cur cursor for
select name from sysobjects where name like 'Position%' and xtype = 'U'
open ptbl_cur

declare @tname varchar(30) -- Table name
declare @sqlstmt varchar(4000)
declare @CurrentState int -- 0 = Stationary, 1 = Moving
declare @speed int -- Speed
declare @vehid varchar(40) -- Vehicle ID
declare @messdate datetime --Message Date
declare @messtime datetime --Message Time
declare @lat float(8) --Latitude
declare @long float(8) --Longitude
declare @StartTime datetime -- initial stop
declare @CurrentDate datetime -- date of stop
declare @StopTime datetime -- end of stop
set @CurrentState = 1 --Assume we start moving, so that if first speed
is zero, we start a "stop"

fetch next from ptbl_cur into @tname

while (@@fetch_status = 0)
begin
set @sqlstmt = 'select VehicleID, MessageDate,
DateADD(Second, MessageTime,mes sagedate)AS MesssageTime, ' +
'Speed, Latitude, Longitude from ' + @tname

CREATE TABLE [dbo].[##GAposition]
([MessageDate] [datetime] NOT NULL ,
[MessageTime] [datetime] NOt NUll ,
[VehicleID] [varchar] (40) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[Latitude] [float] NOT NULL ,
[Longitude] [float] NOT NULL ,
[Speed] [int] NOT NULL ,
) ON [PRIMARY]

Insert INTO ##GAPosition (VehicleID, MessageDate, MessageTime,Spe ed,
Latitude, Longitude)

exec (@sqlstmt)

declare rettbl_cur cursor
for select VehicleID, MessageDate, messagetime, speed,
latitude, longitude from ##gaposition

open rettbl_cur

fetch next from rettbl_cur into @vehid, @messDate, @MessTime, @speed,
@lat, @long

while (@@fetch_status = 0)

begin

if @speed <=3
begin

if @CurrentState = 1
begin
set @StartTime = @messTime
set @CurrentDate = @messDate
set @CurrentState = 0
end

set @StopTime = @messTime
end

if @speed <> 0 and @CurrentState = 0
begin
insert into ReportStopLogDe tailed
(MobileAssetID, StopDate,StopTi me,StartTime,St opDuration,Lati tude,Longitu
de)
values
(@vehid,@Curren tDate,@starttim e,@StopTime,dat ediff(mi,@start time,@StopTi
me),@lat,@long)

set @CurrentState = 1
end

fetch next from rettbl_cur into @vehid, @messDate, @MessTime,
@speed, @lat, @long

End

Drop table [dbo].[##GAposition]

deallocate rettbl_cur

fetch next from ptbl_cur into @tname

End

go
deallocate ptbl_cur

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #5
Dave <Br***********@ gmail.com> wrote in message news:<41******* *************** @news.newsgroup s.ws>...
I just wanted to thank everyone for the help...

Here is a look at the final cursor...

declare ptbl_cur cursor for
select name from sysobjects where name like 'Position%' and xtype = 'U'
open ptbl_cur

declare @tname varchar(30) -- Table name
declare @sqlstmt varchar(4000)
declare @CurrentState int -- 0 = Stationary, 1 = Moving
declare @speed int -- Speed
declare @vehid varchar(40) -- Vehicle ID
declare @messdate datetime --Message Date
declare @messtime datetime --Message Time
declare @lat float(8) --Latitude
declare @long float(8) --Longitude
declare @StartTime datetime -- initial stop
declare @CurrentDate datetime -- date of stop
declare @StopTime datetime -- end of stop
set @CurrentState = 1 --Assume we start moving, so that if first speed
is zero, we start a "stop"

fetch next from ptbl_cur into @tname

while (@@fetch_status = 0)
begin
set @sqlstmt = 'select VehicleID, MessageDate,
DateADD(Second, MessageTime,mes sagedate)AS MesssageTime, ' +
'Speed, Latitude, Longitude from ' + @tname

CREATE TABLE [dbo].[##GAposition]
([MessageDate] [datetime] NOT NULL ,
[MessageTime] [datetime] NOt NUll ,
[VehicleID] [varchar] (40) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[Latitude] [float] NOT NULL ,
[Longitude] [float] NOT NULL ,
[Speed] [int] NOT NULL ,
) ON [PRIMARY]

Insert INTO ##GAPosition (VehicleID, MessageDate, MessageTime,Spe ed,
Latitude, Longitude)

exec (@sqlstmt)

declare rettbl_cur cursor
for select VehicleID, MessageDate, messagetime, speed,
latitude, longitude from ##gaposition

open rettbl_cur

fetch next from rettbl_cur into @vehid, @messDate, @MessTime, @speed,
@lat, @long

while (@@fetch_status = 0)

begin

if @speed <=3
begin

if @CurrentState = 1
begin
set @StartTime = @messTime
set @CurrentDate = @messDate
set @CurrentState = 0
end

set @StopTime = @messTime
end

if @speed <> 0 and @CurrentState = 0
begin
insert into ReportStopLogDe tailed
(MobileAssetID, StopDate,StopTi me,StartTime,St opDuration,Lati tude,Longitu
de)
values
(@vehid,@Curren tDate,@starttim e,@StopTime,dat ediff(mi,@start time,@StopTi
me),@lat,@long)

set @CurrentState = 1
end

fetch next from rettbl_cur into @vehid, @messDate, @MessTime,
@speed, @lat, @long

End

Drop table [dbo].[##GAposition]

deallocate rettbl_cur

fetch next from ptbl_cur into @tname

End

go
deallocate ptbl_cur

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Looks good, except your cursors arent named "boris" :-)

I tend to get a bit of stick about that at work, but I generally work
with cursors that if they had a "meaningful " name, would take longer
to type than the whole process takes... Plus, when people see cursors
name boris, they know that I wrote the code, so they know who to
address questions to.
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
2946
by: Bart Nessux | last post by:
am I doing this wrong: print (time.time() / 60) / 60 #time.time has been running for many hours if time.time() was (21600/60) then that would equal 360/60 which would be 6, but I'm not getting 6 so I'm not doing the division right, any tips?
1
6051
by: Bob | last post by:
thanks in advance, I am using Oracle 9i. I am looping through records via plsql and subtracting 2 timestamp datatypes. myTime := (disconTime - startTime); my problem is I dont understand how to get an aggregated variable out of myTime. What I want is a total amount of time used. I would like to
3
3750
by: vicyankees | last post by:
I am creating a report that has call duration and if i run it over a multi-day span the duration is limited to 24 hours and starts over at zero. is there a way to not limit hours? I am usign the following in my select statement: convert(varchar(12), dateadd(s, SUM(ISNULL(Calls.OutgoingCallDuration,0)), '19900101'), 108) AS
3
25188
by: Arne Gemsa | last post by:
Hi, I want to get a starttime. To get this I have to subtract the runtime from a unit e.g. 08:40:15 from a date e.g. 2007-03-29 16:48:30. So the starttime is 2007-03-29 08:08:15. Is there any function in php to do so, or must I do it by myself? thx
1
2791
mangat
by: mangat | last post by:
Hi guy's i'm stuck in a similar problem. The problem is that i have two fields one shows the LogIn time of the user and other shows the Duration since the user LogIn and now i want to store the logout time which is addition of LogIn time and Duration How i achive this.Plz Help me. thanx In advance. Mangat Phogat
4
2419
by: Brian | last post by:
I have a 2000/2002 Access db that I use to collect and store my exercisetime using a form to enter. I wanted to see a summary of the total timefor each exercise so I have a subform that does this. Only issue is thatwhen I go over 24 hrs I get the infamous summing time issue. It would bereally easy if Access used the Excel :nn format, but it...
0
1250
by: Yew12 | last post by:
I'm trying to use PL/SQL to create a trigger that will stop bookings. Based on a duration field and a start time. I have had a go at trying to do this but I'm sure that im well off from where I need to be. Create or Replace Trigger multiple_appointments Before insert on appointment Begin IF (NEW.toNumber(ADATE) between...
8
2800
by: Lewe22 | last post by:
I have 2 fields in a table called and . They are both in short time format. I am displaying these in a form with a calculated field to show the time duration as well. (ie. = -) this is also displayed in short time format. Example start time end time duration 12:00 12:50 00:50 12:00 13:10 ...
6
2817
by: Jim Mandala | last post by:
Using MS Access 2003 with SQL Server 2005 as a backend: I am trying to automatically check for collisions in a table of appointments with an appointment currenlty being saved from the form "FormAppt". I do this by calculating an end time form the start time and duration. Then I have two queries. The first one, "QueryA", finds all the...
0
7779
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7693
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8202
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7781
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6418
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3732
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2208
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1301
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1040
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.