Calculate Time difference

Hello

I have a table GPSDATA which consists of
VehicleNo,Speed,CurrentDate,slno,status

This data gets updated every minute.And every minute the slno(serial no) will be incremented automatically.
The status field shows whether the vehicle is moving or idle(That is neither in stop nor in moving) position

The user will choose the vehicleno(From the Front End which is VB.net 2005)
and I want to display the time difference between the current time and the latest time of the vehicle.

I will check that if the status is in idle and the time difference is more than 30 minutes then make the status as stop.

Kindly let me know how to frame this query.

Thanks
cmrhema

Quote:

Originally Posted by cmrhema

Hello

I have a table GPSDATA which consists of
VehicleNo,Speed,CurrentDate,slno,status

This data gets updated every minute.And every minute the slno(serial no) will be incremented automatically.
The status field shows whether the vehicle is moving or idle(That is neither in stop nor in moving) position

The user will choose the vehicleno(From the Front End which is VB.net 2005)
and I want to display the time difference between the current time and the latest time of the vehicle.

I will check that if the status is in idle and the time difference is more than 30 minutes then make the status as stop.

Kindly let me know how to frame this query.

Thanks
cmrhema

Get the latest date of the vehicle using:

SELECT TOP 1
@.slno = slno,
@.lastDate = CurrentDate
FROM YourTable
WHERE VehicleNo = @.vehicleno
ORDER BY CurrentDate DESC

Then compare the dates of the records using:

IF DATEDIFF(minute, @.lastDate, GETDATE()) > 30 BEGIN
UPDATE YourTable
SET status = 'stop'
WHERE slno = @.slno
END|||

Quote:

Originally Posted by DonlonP

Get the latest date of the vehicle using:

SELECT TOP 1
@.slno = slno,
@.lastDate = CurrentDate
FROM YourTable
WHERE VehicleNo = @.vehicleno
ORDER BY CurrentDate DESC

Then compare the dates of the records using:

IF DATEDIFF(minute, @.lastDate, GETDATE()) > 30 BEGIN
UPDATE YourTable
SET status = 'stop'
WHERE slno = @.slno
END

Thanks solved the problem

Caching results of Stored Procedure

I was wondering if there is anyway to cache the results of a stored procedure in order to gain speed ?

Or do I need to do something else clever ?

Here is my SQL

procedure spGetProcedureParams(v_procname in varchar2,
v_packagename in varchar2,
CurRef out typeRefCur,
v_errcode out integer,
v_errmesg out varchar2)

is
begin
v_errcode :=0;
open CurRef for
select argument_name,
data_type,
NVL(pls_type,'REF'),
in_out
from user_arguments
where upper(object_name) = upper(v_procname)
and upper(package_name) = upper(v_packagename)
order by sequence;
exception
when others then
v_errcode :=SQLCODE;
v_errmesg :=SQLERRM;
end spGetProcedureParams;

end pkgUtils;
/I'm not really sure what you are looking for. You could fetch the data into a collection like an index-by table. Whether this will improve performance depends on what you are going to do with it next.