Help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with query

Author

Topic

Diablos
Starting Member

10 Posts

Posted - 2013-06-07 : 13:18:43

Hi there,

I'm having trouble with the following query, the goal is to get the duration of time that the ignition is on but the car is stopped.
My data is like this:
VehicleID,LocationDate,Speed,DistanceFromLastLocation,IsignitionOn
1756,01-01-2013 09:00:00,0,0,1
1756,01-01-2013 09:01:00,0,0,1
1756,01-01-2013 09:02:00,0,0,1
1756,01-01-2013 09:03:00,15,5,1
1756,01-01-2013 09:04:00,20,15,1
1756,01-01-2013 09:05:00,0,0,1
1756,01-01-2013 09:06:00,0,0,1
What i want to do is sum the time of locationdate whenever the speed=0 and the DistanceFromLastLocation=0 and IsignitionOn=1,then show the result, and move on to the next.

So the result should be:
VehicleID,Date,Start Hour,End Hour,Time with Ignition on
1756,01-01-2013,09:00:00,09:02:00,2
1756,01-01-2013,09:05:00,09:06:00,1

Thanks for the help,
Sorry about the bad English.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-08 : 03:29:14

[code]
SELECT t.VehicleID,DATEADD(dd,DATEDIFF(dd,0,t.LocationDate),0),
CONVERT(varchar(8),MIN(t.LocationDate),108) AS StartHour,
COVERT(varchar(8),MAX(LocationDate),108) AS EndHour,
SUM(IsignitionOn) -1 AS [TimewithIgnitionOn]
FROM Table t
OUTER APPLY (SELECT MIN(LocationDate) AS MinDate
FROM table
WHERE VehicleID = t.VehicleID
AND LocationDate > t.LocationDate
AND Speed <> 0
AND DistanceFromLastLocation <> 0
AND DATEDIFF(dd,0,LocationDate) = DATEDIFF(dd,0,t.LocationDate)
)t1
WHERE t.Speed =0
AND t.DistanceFromLastLocation = 0
AND t.IsignitionOn =1
GROUP BY t.VehicleID,DATEADD(dd,DATEDIFF(dd,0,t.LocationDate),0),t1.MinDate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Diablos
Starting Member

10 Posts

Posted - 2013-06-19 : 05:32:37

Thanks a lot it worked :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 05:42:09

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources