Time Tracking

[lfcmd]

Hi Group,

I have a tracker device and need generate a report which shows the start time, stop time and resume time. the veicle is classed as stoped when the speed is returned three tiems with a speed of zero

Name Time Locat Speed
Unit1 10:00 Mallow 40
Unit1 10:01 Yail 40
Unit1 10:02 Yail 40
Unit1 10:03 Yail 40
Unit1 10:04 Yail 0
Unit1 10:05 Yail 0
Unit1 10:06 Yail 0
Unit1 11:12 Mallow 36
Unit1 11:13 Mallow 36
Unit1 11:02 Mallow 0
Unit1 11:03 Mallow 0
Unit1 11:04 Mallow 0

From the data above i would need to produce the following

Name Time Action Location Motion time Idle Time
Unit 1 10:00 Started Mallow 0.00
Unit 1 10:05 Jorrney Yail 0.5
Unit 1 10:06 Stopped Yail 1.6
Unit 1 11:12 Resumed Mallow

in the above result, motion time of .5 represents =5mins, and idle time of 1.6 represents i hour 6 mins , motion time is decided from when car starts moving till the third zero speed is returned in a row and stopped time set for the third zero speed returned in a row till the next time the veichle is moving, The location is decided aginst where the car was for the longest part of the journey,
when the veichle is moving is sends an update every minute to the database until the car is not moving for three minutes i.e its speed i zero for three updates, and starts sending again when the speed goes above zero miles per hour

Any help on this would be greatley appreciated.

 

[vongrunt]

Shouldn't there be one more Action='Stopped' record in result (last 3 rows in input data also have speed 0)?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[lfcmd]

Yes sorry shoudl have read as follows


Name Time Locat Speed
Unit1 10:00 Mallow 40
Unit1 10:01 Yail 40
Unit1 10:02 Yail 40
Unit1 10:03 Yail 40
Unit1 10:04 Yail 0
Unit1 10:05 Yail 0
Unit1 10:06 Yail 0
Unit1 11:00 Mallow 36
Unit1 11:01 Mallow 36
Unit1 11:02 Mallow 0
Unit1 11:03 Mallow 0
Unit1 11:04 Mallow 0

From the data above i would need to produce the following

Name Time Action Location Motion time Idle Time
Unit 1 10:00 Started Mallow 0.00
Unit 1 10:05 Journey Yail 0.5
Unit 1 10:06 Stopped Yail .54
Unit 1 11:00 Resumed Mallow
Unit 1 11:03 Journey Mallow .3
Unit 1 11:04 Stopped Mallow

 

[gmmastros]

The data changed on me.

Here's what I have so far. It's not complete, but hopefully you'll see the method and be able to expand on it.

[green]-- Setting up some data[/green]
SET NOCOUNT ON
Declare @Data Table(Name VarChar(20), Time DateTime, Locat Varchar(20), Speed Integer)

Insert Into @Data Values('Unit1','10:00','Mallow',40)
Insert Into @Data Values('Unit1','10:01','Yail',40 )
Insert Into @Data Values('Unit1','10:02','Yail',40 )
Insert Into @Data Values('Unit1','10:03','Yail',40 )
Insert Into @Data Values('Unit1','10:04','Yail',0 )
Insert Into @Data Values('Unit1','10:05','Yail',0 )
Insert Into @Data Values('Unit1','10:06','Yail',0 )
Insert Into @Data Values('Unit1','11:12','Mallow',36)
Insert Into @Data Values('Unit1','11:13','Mallow',36)
Insert Into @Data Values('Unit1','11:02','Mallow',0)
Insert Into @Data Values('Unit1','11:03','Mallow',0)
Insert Into @Data Values('Unit1','11:04','Mallow',0)

-- The query
Declare @Temp Table (RowId Integer Identity(1,1), Name VarChar(20), Time DateTime, Locat VarChar(20), Speed Integer)
Declare @Output Table(RowId Integer Identity(1,1), Name VarChar(20), Time DateTime, Action VarChar(20), Locat VarChar(20))

Insert
Into   @Temp(Name, Time, Locat, Speed)
Select Name, DateAdd(Minute, -1, Min(Time)), NULL, 0
From   @Data
Group By Name

Insert
Into   @Temp(Name, Time, Locat, Speed)
Select Name, Time, Locat, Speed
From   @Data
Where  Name = 'Unit1'
Order By Time

Insert 
Into    @Output(Name, Time, Action, Locat)
Select	Name, Time, Action, Locat
From	(
		Select 	T4.Name,
				T4.Time,
				'Stopped' As Action,
				T4.Locat
		From 	@Temp T1
				Inner Join @Temp T2 
					On 	T1.RowId = T2.RowId - 1
				Inner Join @Temp T3 
					On 	T2.RowId = T3.RowId - 1
				Inner Join @Temp T4 
					On 	T3.RowId = T4.RowId - 1
		Where   T1.Speed <> 0
		        And T2.Speed = 0
		        And T3.Speed = 0
		        And T4.Speed = 0
		--Order By T1.Time
		
		Union All
		
		Select T2.Name,
		       T2.Time,
		       'Moving' As Action,
		       T2.Locat
		From   @Temp T1
		       Inner Join @Temp T2
		         On T1.RowId = T2.RowId - 1
		Where  T1.Speed = 0
		       And T2.Speed <> 0
	) A 
Order by time

Insert
Into   	@Output(Name, Time, Action, Locat)
Select  B.Name, B.Time, 'Journey', B.Locat
From 	@output A
		Inner join @Output B
			On  A.Name = B.Name
			And A.RowId = B.RowId - 1
Where   A.Action = 'Moving'
		And B.Action = 'Stopped'

select Name, Time, Action, Locat from @Output Order By Time

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lfcmd]

Cheers George, look good should be able to tweak it to what i need
would there be much involved in getting the average speed for each journy ?

 

[gmmastros]

Average speed with or without the 0 speeds? When the vehicle is at a traffic light, obviously the speed would be zero. Do you want those speeds as part of your average?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lfcmd]

the average speed excluding all zero speeds

cheers George

 

[gmmastros]

There may be a better way, but for now, try this query. Replace the last query (from my previous post) with this one.

select 	O.Name, 
		O.Time, 
		O.Action, 
		O.Locat,
		Average.AverageSpeed
From 	@Output As O
		Left Join (
			Select  B.Name, 
					B.Time,
					Sum(D.Speed) / Sum(Case When D.Speed > 0 Then 1 Else 0 End) As AverageSpeed
			From 	@output A
					Inner join @Output B
						On  A.Name = B.Name
						And A.RowId = B.RowId - 1
					Inner Join @Data D 
						On  A.Name = D.Name
						And D.Time Between A.Time And B.Time
			Where   A.Action = 'Moving'
					And B.Action = 'Stopped'
			Group By B.Name, B.Time
			) As Average 
			On 	O.Time = Average.Time
			And O.Name = Average.Name
			And O.Action = 'Journey'
 Order By O.Time, O.Action

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom