Alon
Posts: 180
Joined: Wed Sep 01, 2004 3:48 pm

Trip and Idle report with Ignition

Wed Nov 12, 2014 5:56 am

Note: this report has been replaced by a more efficient version. How to setup here

Hi,

When saving the query code mentioned in the blog post I'm getting the following error:
"'TIMEDIFF' is not a recognized built-in function name. Incorrect syntax near the keyword 'AS'. Incorrect syntax near the keyword 'AS'. Incorrect syntax near the keyword 'AND'. Incorrect syntax near the keyword 'AND'."

My DB is MSSQL, might be that the code above is for MySQL?

Thanks.

User avatar
juanjogpsgate
GpsGate
Posts: 25
Joined: Wed Oct 08, 2014 12:49 pm
Location: Sweden
Contact: Website

RE: Trip and Idle report with Ignition

Thu Nov 13, 2014 4:54 pm

Hello Alon,

yes, the guide was originally intended to work on a MySQL database.

To make this query work in MsSQL there are some differences mainly in the expressions names (TIMEDIFF is DATEDIFF in MsSQL) and in the order you put the arguments (as WHEN).

Below you'll find the code for MsSQL.

One remark only:

- when you are going to edit the report to add this code, you'll see a selector for MySQL and another for T-SQL. Paste the code in the latter and save it.

The code:

Code: Select all

SELECT 
	CAST([PeriodStart] AS DATETIME) AS PeriodStart,
	CAST([PeriodEnd] AS DATETIME) AS PeriodEnd,
	[ApplicationID] AS ApplicationID,
	Users.Username,
	Users.Name,
	DATEDIFF(SECOND, Distance01withIgnition.SamplingStart, Distance01withIgnition.SamplingEnd) AS Duration,
	
	CASE WHEN Distance01withIgnition.FatPointState = 'run' AND Distance01withIgnition.Ignition = '1'
		THEN DATEDIFF(SECOND, Distance01withIgnition.SamplingStart, Distance01withIgnition.SamplingEnd)
		ELSE 0 END as TripTime,
	CASE WHEN Distance01withIgnition.FatPointState = 'idle' AND Distance01withIgnition.Ignition = '1'
		THEN DATEDIFF(SECOND, Distance01withIgnition.SamplingStart, Distance01withIgnition.SamplingEnd)
		ELSE 0 END AS IdleDuration,
	CASE WHEN Distance01withIgnition.Ignition = '0'
		THEN DATEDIFF(SECOND, Distance01withIgnition.SamplingStart, Distance01withIgnition.SamplingEnd)
		ELSE 0 END AS ParkingTime,
		
	Distance01withIgnition.SamplingStart,
	Distance01withIgnition.SamplingEnd,
	Distance01withIgnition.UserID,
	Distance01withIgnition.StartAddress,
	Distance01withIgnition.StopAddress,
	Distance01withIgnition.DistanceGps,
	Distance01withIgnition.DistanceOdo,
	Distance01withIgnition.MaxSpeed,
	CASE WHEN Distance01withIgnition.DistanceGps > 0 AND DATEDIFF(SECOND, Distance01withIgnition.SamplingStart, Distance01withIgnition.SamplingEnd) > 0 THEN
	Distance01withIgnition.DistanceGps / DATEDIFF(SECOND, Distance01withIgnition.SamplingStart, Distance01withIgnition.SamplingEnd)
	ELSE
	0
	END AS AvgSpeed,
	Distance01withIgnition.FatPointState,
	Distance01withIgnition.Ignition,
	Distance01withIgnition.Fuel
FROM Distance01withIgnition
	JOIN Users ON Users.UserID = Distance01withIgnition.UserID
WHERE Distance01withIgnition.UserID IN (SELECT UserID FROM TagUsers WHERE TagUsers.TagID IN [TagID])
	AND Distance01withIgnition.SamplingStart >= [PeriodStart] 
	AND Distance01withIgnition.SamplingStart <= [PeriodEnd]
	AND Distance01withIgnition.FatPointState != 'sleep'
	ORDER BY Distance01withIgnition.UserID, Distance01withIgnition.SamplingStart

Alon
Posts: 180
Joined: Wed Sep 01, 2004 3:48 pm

RE: Trip and Idle report with Ignition

Thu Nov 13, 2014 7:29 pm

Thanks, I could finish the setting up the report.
When tested, the rendering part goes forever. Any idea why?

Thanks.

qtech
Posts: 80
Joined: Sun Mar 17, 2013 12:19 am

RE: Trip and Idle report with Ignition

Mon Nov 17, 2014 8:08 pm

Hello spoerte've followed all the steps but when you preview can not see the data in the amount of time parked either in the column showing the status.

I may be doing wrong?

User avatar
juanjogpsgate
GpsGate
Posts: 25
Joined: Wed Oct 08, 2014 12:49 pm
Location: Sweden
Contact: Website

RE: Trip and Idle report with Ignition

Wed Nov 19, 2014 1:32 pm

Alon, as you are a customer of ours and your case would need to take a closer look, I have contacted you already through support.

Qtech, it might be the issue with the quoting format.

I modified the blog entry so you can copy the text without formatting. You'll be able to see that now the single quotes are straight and not shaped.

Regards.

qtech
Posts: 80
Joined: Sun Mar 17, 2013 12:19 am

RE: Trip and Idle report with Ignition

Wed Nov 19, 2014 6:35 pm

hi

@juanjogpsgate

Thanks for responding, I can now view showing the state but they are parking.

regards

rajiv
Posts: 110
Joined: Thu Nov 27, 2014 2:27 pm

RE: Trip and Idle report with Ignition

Thu Nov 27, 2014 2:32 pm

Hi,

After changing the query as suggested I am not able to save it. I do not get any error when I Save the query but when I try to execute it the old query is getting executed. Any idea why this may be happening ?

Regards

User avatar
juanjogpsgate
GpsGate
Posts: 25
Joined: Wed Oct 08, 2014 12:49 pm
Location: Sweden
Contact: Website

RE: Trip and Idle report with Ignition

Mon Dec 01, 2014 12:49 pm

Hi,
a few improvements and corrections has been added to the MySQL and MsSQL code.
Please, copy and paste it into your customized report.

Make sure you have saved the query after the modifications, and also activate your report as described in this link:

http://gpsgate.com/blogComment/activate ... _server_30

If you have created a copy of the Trip and Idle report, you may want to rename the report and descriptions to avoid misunderstandings when enabling it in your application.

rajiv
Posts: 110
Joined: Thu Nov 27, 2014 2:27 pm

RE: Trip and Idle report with Ignition

Tue Dec 02, 2014 8:37 am

[I have taken the query given in this discussion thread. I had saved the query sucessfully but still when I execute it the original query from Trip and Idle report is getting executed.

I tried creating a report from empty and was able to save and execute the same querry sucessfully. It is only when I am trying to modify existing template that I am facing this problem. I had renamed the report query and report before saving]Originally posted by juanjogpsgate

Hi,
a few improvements and corrections has been added to the MySQL and MsSQL code.
Please, copy and paste it into your customized report.

Make sure you have saved the query after the modifications, and also activate your report as described in this link:

http://gpsgate.com/blogComment/activate ... _server_30

If you have created a copy of the Trip and Idle report, you may want to rename the report and descriptions to avoid misunderstandings when enabling it in your application.
[/quote]

User avatar
juanjogpsgate
GpsGate
Posts: 25
Joined: Wed Oct 08, 2014 12:49 pm
Location: Sweden
Contact: Website

RE: Trip and Idle report with Ignition

Tue Dec 02, 2014 11:38 am

Hi rajiv,

try reprocessing the report and tracking data in the Site Admin -> Reporting -> Reprocess, (and reprocess both, tracking and report). Select a given date where you are sure you had received the Ignition signal.

You have to recheck the installation steps to verify that you actually are receiving the Ignition signal from your device, you have mapped it into the Device Mapper, etc.

Verify the blog steps and it should work. If you still believe it doesn't, submit your questions to support@gpsgate.com.

rajiv
Posts: 110
Joined: Thu Nov 27, 2014 2:27 pm

RE: Trip and Idle report with Ignition

Tue Dec 02, 2014 3:00 pm

Johan,

I am not able to Reprocess option under Site Admin -> Reporting.
However I retried the steps given in the blog but this time rather than choosing existing reporting query distancedetail and modifying it, I pasted the query given in the blog int he empty query window and saved it under a new name. It is working fine now. Thanks.
[:)]

rajiv
Posts: 110
Joined: Thu Nov 27, 2014 2:27 pm

RE: Trip and Idle report with Ignition

Fri Dec 12, 2014 12:10 pm

I have customised the report, but some how the data is not shown correctly. Below is a sample of the data in the new report.

10-12-2014 10:16:29 12:39:00 02:22:31 99 km 57.9 km/h 41.5 km/h run
10-12-2014 12:39:00 12:39:00 00:00:00 0.0 km 0.0 km/h 0.0 km/h parking
10-12-2014 13:52:41 13:52:41 00:00:00 0.0 km 0.0 km/h 0.0 km/h idle
10-12-2014 13:53:10 13:53:10 00:00:00 0.0 km 0.0 km/h 0.0 km/h parking
10-12-2014 13:55:14 13:55:14 00:00:00 0.0 km 0.0 km/h 0.0 km/h idle
10-12-2014 13:55:37 14:48:12 00:52:35 0.0 km 0.6 km/h 0.0 km/h parking
10-12-2014 14:48:12 14:56:33 00:08:21 4 km 46.2 km/h 28.8 km/h run

As you can see I am getting a few gaps in the sample start/sample end times for which records are missing. I am also getting few records with zero duration.

The data for the sampe time period from the original Trip and Idle report looks like this.

10-12-2014 10:16:29 12:39:00 02:22:31 57.9 km/h 41.5 km/h run
10-12-2014 12:39:00 14:48:12 02:09:12 0.6 km/h 0.0 km/h idle
10-12-2014 14:48:12 14:56:33 00:08:21 46.2 km/h 28.8 km/h run

what could be the reason. The query is as follows.

SELECT
CAST([PeriodStart] AS DATETIME) AS PeriodStart,
CAST([PeriodEnd] AS DATETIME) AS PeriodEnd,
[ApplicationID] AS ApplicationID,
Users.Username,
Users.Name,
DATEDIFF(SECOND, Distance02withIgnition.SamplingStart, Distance02withIgnition.SamplingEnd) AS Duration,

CASE WHEN Distance02withIgnition.FatPointState = 'run' AND Distance02withIgnition.Ignition = '1'
THEN DATEDIFF(SECOND, Distance02withIgnition.SamplingStart, Distance02withIgnition.SamplingEnd)
ELSE 0 END as TripTime,
CASE WHEN Distance02withIgnition.FatPointState = 'idle' AND Distance02withIgnition.Ignition = '1'
THEN DATEDIFF(SECOND, Distance02withIgnition.SamplingStart, Distance02withIgnition.SamplingEnd)
ELSE 0 END AS IdleDuration,
CASE WHEN Distance02withIgnition.FatPointState = 'idle' AND Distance02withIgnition.Ignition = '0'
THEN DATEDIFF(SECOND, Distance02withIgnition.SamplingStart, Distance02withIgnition.SamplingEnd)
ELSE 0 END AS ParkingTime,

Distance02withIgnition.SamplingStart,
Distance02withIgnition.SamplingEnd,
Distance02withIgnition.UserID,
Distance02withIgnition.StartAddress,
Distance02withIgnition.StopAddress,
Distance02withIgnition.DistanceGps,
Distance02withIgnition.DistanceOdo,
Distance02withIgnition.MaxSpeed,
CASE WHEN Distance02withIgnition.DistanceGps > 0 AND DATEDIFF(SECOND, Distance02withIgnition.SamplingStart, Distance02withIgnition.SamplingEnd) > 0 THEN
Distance02withIgnition.DistanceGps / DATEDIFF(SECOND, Distance02withIgnition.SamplingStart, Distance02withIgnition.SamplingEnd)
ELSE
0
END AS AvgSpeed,
Distance02withIgnition.FatPointState,
Distance02withIgnition.Ignition,
Distance02withIgnition.Fuel
FROM Distance02withIgnition
JOIN Users ON Users.UserID = Distance02withIgnition.UserID
WHERE Distance02withIgnition.UserID IN (SELECT UserID FROM TagUsers WHERE TagUsers.TagID IN [TagID])
AND Distance02withIgnition.SamplingStart >= [PeriodStart]
AND Distance02withIgnition.SamplingStart <= [PeriodEnd]
AND Distance02withIgnition.FatPointState != 'sleep'
ORDER BY Distance02withIgnition.UserID, Distance02withIgnition.SamplingStart

The data provider is set up as

SamplingStart UTC Min RemoveEdit
SamplingEnd UTC Max RemoveEdit
UserID UserID Last Value RemoveEdit
StartAddress Address Start Address RemoveEdit

StopAddress Address Stop Address RemoveEdit

DistanceGps TrackPoint Distance (GPS) RemoveEdit

DistanceOdo OdometerAcc Distance (Odometer) RemoveEdit

MaxSpeed Speed Max RemoveEdit

AvgSpeed Speed Average RemoveEdit

Fuel Fuel level Fuel Consumption RemoveEdit

Ignition Ignition First Value RemoveEdit

FatPointState FatPointState Last Value RemoveEdit

rajiv
Posts: 110
Joined: Thu Nov 27, 2014 2:27 pm

RE: Trip and Idle report with Ignition

Fri Dec 12, 2014 12:26 pm

Further the ignition values in track point data is as follows

12:39:00 Off
13:52:41 On
13:53:10 off
13:55:14 on
13:55:37 Off
14:48:12 on

Agustin94
Posts: 24
Joined: Wed Aug 05, 2015 12:35 am

RE: Trip and Idle report with Ignition

Wed Jan 27, 2016 1:53 am

Hi,
I am trying to configure this report, but when I try to save the query I have the next error, someone knows how to resolved?

"ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.5.20]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CAST((SELECT '') AS DATETIME) AS PeriodStart, CAST((SELECT '') AS DATETIME) AS P' at line 1"<font color="red"></font id="red">

Strack01
Posts: 27
Joined: Thu Jun 09, 2016 7:02 pm

RE: Trip and Idle report with Ignition

Thu Jun 09, 2016 7:02 pm

Hi i am getting the same error;
ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.5.20]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CAST((SELECT '') AS DATETIME) AS PeriodStart, CAST((SELECT '') AS DATETIME) AS P' at line 1

any got a solution to this?

Return to “GpsGate for Developers”