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

"Dave" <BroncosBuck99@gmail.com> wrote in message
news:dfc28184.0409290902.7a7edbe7@posting.google.c om...
>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

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!

Dave <BroncosBuck99@gmail.com> wrote in message news:<415b26fc$0$26086$c397aba@news.newsgroups.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,<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

Anyone see a problem with this?

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,StopDur ation,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 ***
Don't just participate in USENET...get rewarded for it!

Dave <BroncosBuck99@gmail.com> wrote in message news:<41616c5c$0$26144$c397aba@news.newsgroups.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,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,StopDur ation,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 ***
> 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.