How to frame a query based on the following prblm

Question

Post reply

How to frame a query based on the following prblm

cmrhema

SSCommitted

Points: 1558
More actions
September 16, 2008 at 3:22 am

#190839

Hi,
I have a table consisiting of the following fields
Vehicle No TrackTime
ABC 01/10/2008 01:00
ABCD 01/10/2008 01:00
ABC 01/10/2008 01:01
ABC 01/10/2008 01:02
ABC 01/10/2008 01:03
ABC 01/10/2008 01:07
ABCD 01/10/2008 01:10
ABCD 01/10/2008 01:12
ABCD 01/10/2008 01:13
ABCD 01/10/2008 01:14
ABC 01/10/2008 01:15
ABCD 01/10/2008 01:15
ABC 01/10/2008 01:16
ABC 01/10/2008 01:17
ABCD 01/10/2008 01:25
ABCD 01/10/2008 01:30
In this manner I have many vehciles which will send every minute the data.
I want to list out all the vehicles and the corresponding tracking time where the time difference between their two corresponding records is greater than 5 minutes for the same vehicle
How to do that
Kindly help
Regards
cmrhema

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

HI there,

Could you please show us what you want your out put to look like?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

cmrhema SSCommitted Points: 1558 More actions · Answer 2

first thanks for reply and secondly i would like my answer as below

ABC 01/10/2008 01:07

ABC 01/10/2008 01:15

ABCD 01/10/2008 01:00

ABCD 01/10/2008 01:10

ABCD 01/10/2008 01:15

ABCD 01/10/2008 01:25

ABCD 01/10/2008 01:30

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

Hi there,

Is the difference between:

01/10/2008 01:00

AND

01/10/2008 01:17

Not also 5mins?

Also check this link out for tips on getting the best out of your posts:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

cmrhema SSCommitted Points: 1558 More actions · Answer 4

Yes, but I think I have not placed my question properly.

The vehicle will send every minute data, so if i group by vehicle , i will get each and every records of all the vehicles order by the trackingtime.

Now for eg a vehicle named ABC is not sending signal from 01:00 to 01:10, and then sends signal every minute, after some time say from 05:00 to 05:13 it once again does not send any signal, then I want to capture these two records. and display as below

ABC 01:00

ABC 01:10

ABC 05:00

ABC 05:13

Similarly for all the vehicles.

So it will be easier for me to find the list of vehicles tat did not send signal for the timespan of more than 5 minutes.

regards

cmrhema

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

with cte as (

select VehicleNo, TrackTime, row_number() over(partition by VehicleNo order by TrackTime) as rn

from mytable)

select a.VehicleNo,a.TrackTime

from CTE a

where not exists (select * from CTE b where b.VehicleNo=a.VehicleNo and b.rn=a.rn+1 and b.TrackTime <= dateadd(minute,5,a.TrackTime))

order by a.VehicleNo,a.TrackTime

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 6

Hi Mark,

You solution seems to be missing some of the results.

Here is a solution that seems to work, but I have a feeling that with a large data set this is going to slow down so I will work on Improving it.

[font="Courier New"]

DECLARE @tmp TABLE

(

[VehicleNo] VARCHAR(4),

[TrackTime] DATETIME

)

INSERT INTO @tmp

SELECT 'ABC','01/10/2008 01:00' UNION ALL

SELECT 'ABCD','01/10/2008 01:00' UNION ALL

SELECT 'ABC','01/10/2008 01:01' UNION ALL

SELECT 'ABC','01/10/2008 01:02' UNION ALL

SELECT 'ABC','01/10/2008 01:03' UNION ALL

SELECT 'ABC','01/10/2008 01:07' UNION ALL

SELECT 'ABCD','01/10/2008 01:10' UNION ALL

SELECT 'ABCD','01/10/2008 01:12' UNION ALL

SELECT 'ABCD','01/10/2008 01:13' UNION ALL

SELECT 'ABCD','01/10/2008 01:14' UNION ALL

SELECT 'ABC','01/10/2008 01:15' UNION ALL

SELECT 'ABCD','01/10/2008 01:15' UNION ALL

SELECT 'ABC','01/10/2008 01:16' UNION ALL

SELECT 'ABC','01/10/2008 01:17' UNION ALL

SELECT 'ABCD','01/10/2008 01:25' UNION ALL

SELECT 'ABCD','01/10/2008 01:30'

;WITH MyCTE(ROWNUM,[VehicleNo],[TrackTime])

AS

(SELECT

ROW_NUMBER() OVER (PARTITION BY GETDATE() ORDER BY [VehicleNo],[TrackTime])

,[VehicleNo],[TrackTime]

FROM @tmp)

SELECT *

FROM MyCTE

WHERE ROWNUM IN

(SELECT [1].[RowNUM]

FROM MyCTE [1]

LEFT JOIN MyCTE [2]

ON [1].ROWNUM = [2].ROWNUM + 1

AND [1].[VehicleNo] = [2].[VehicleNo]

WHERE DATEDIFF(mi,[2].[TrackTime],[1].[TrackTime]) >=5

UNION

SELECT [2].[RowNUM]

FROM MyCTE [1]

LEFT JOIN MyCTE [2]

ON [1].ROWNUM = [2].ROWNUM + 1

AND [1].[VehicleNo] = [2].[VehicleNo]

WHERE DATEDIFF(mi,[2].[TrackTime],[1].[TrackTime]) >=5 )[/font]

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

cmrhema SSCommitted Points: 1558 More actions · Answer 7

Thanks Mark for your reply.

Checked out. It works.

Regards

Hemalatha

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 8

Sorry Mark,

I didn't realise it need to greater than 5 as opposed to greater than or equal too :hehe:

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life