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