Stop time duration problem -- sql server (alternate)

sql server (alternate) : Stop time duration problem

BroncosBuck99 NO[at]SPAM gmail.com
9/29/2004 10:02:39 AM

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
9/29/2004 9:19:57 PM

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 ***

Simon Hayes
9/29/2004 9:37:41 PM

[quoted text, click to view]

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

Damien_The_Unbeliever NO[at]SPAM hotmail.com
10/1/2004 6:39:06 AM

[quoted text, click to view]

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,<Other 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,<Other fields>)
end

Dave
10/4/2004 3:29:32 PM

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,messagedate)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_General_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,Speed,
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 ReportStopLogDetailed
(MobileAssetID,StopDate,StopTime,StartTime,StopDuration,Latitude,Longitu
de)
values
(@vehid,@CurrentDate,@starttime,@StopTime,datediff(mi,@starttime,@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 ***

Damien_The_Unbeliever NO[at]SPAM hotmail.com
10/5/2004 1:00:40 AM

[quoted text, click to view]

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

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

View Other Months
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007

home · blog · groups

Questions? Comments? Contact the dn