Sql Query for Co-related Subgroups.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

I've vehicle tracking data, I want to create Vehicle stoppage report. Help me to write sql query for that. Actual Data and resultant data are like Table1 and Table2 respectively.

TABLE 1

CSS

VehicleId   UnixTimeStamp           Speed
1           17-01-2015 12:00:05       12
1           17-01-2015 12:00:06       0
1           17-01-2015 12:00:07       0
1           17-01-2015 12:00:08       0
1           17-01-2015 12:00:09       5
1           17-01-2015 12:00:10       8
1           17-01-2015 12:00:11       0
1           17-01-2015 12:00:12       0
1           17-01-2015 12:00:13       0
1           17-01-2015 12:00:14       7
2           17-01-2015 12:00:05       10
2           17-01-2015 12:00:06       0
2           17-01-2015 12:00:07       0
2           17-01-2015 12:00:08       0
2           17-01-2015 12:00:09       12
2           17-01-2015 12:00:10       0
2           17-01-2015 12:00:11       0
2           17-01-2015 12:00:12       10

TABLE 2

CSS

Vehicield	Stopped From	                 Stopped To	        Duration (Sec)
1	        17-01-2015 12:00:06	         17-01-2015 12:00:08	         3
1	        17-01-2015 12:00:11	         17-01-2015 12:00:13	         3
2	        17-01-2015 12:00:06	         17-01-2015 12:00:08	         3
2	        17-01-2015 12:00:10	         17-01-2015 12:00:11	         2

Posted 17-Jan-15 1:28am

Mohd Arshad Malik

Updated 17-Jan-15 1:56am

v2

Add a Solution

Comments

Wendelius 17-Jan-15 7:48am

There's your data but what do you want to select? Please provide an example of the desired output

Mohd Arshad Malik 17-Jan-15 7:56am

Desired output is in Table2.

2 solutions

Solution 2

I tried this...

SQL

declare @t as table (vehicleid INT, unixtimestamp DATETIME, speed int)

INSERT INTO @t VALUES(1, '2015-01-17 12:00:05', 12)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:06', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:07', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:08', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:09', 5)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:10', 8)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:11', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:12', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:13', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:14', 7)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:05', 10)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:06', 0)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:07', 0)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:08', 0)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:09', 12)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:10', 0)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:11', 0)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:12', 10)


select start.unixtimestamp,
       stop.unixtimestamp
from @t start,
     @t stop
where start.speed        = 0
and   stop.vehicleid     = start.vehicleid
and   stop.unixtimestamp > start.unixtimestamp
and   stop.speed         = 0
and   exists (select 1 
              from  @t prev
              where prev.vehicleid     = start.vehicleid
              and   prev.unixtimestamp = (select max(p2.unixtimestamp)
                                          from   @t p2
                                          where  p2.vehicleid = start.vehicleid
                                          and    p2.unixtimestamp < start.unixtimestamp)
              and   prev.speed > 0) 
and not exists (select 1
                from   @t p3
                where  p3.vehicleid     = start.vehicleid
                and    p3.unixtimestamp > start.unixtimestamp
                and    p3.unixtimestamp < stop.unixtimestamp
                and    p3.speed         > 0)

but results are not correct...two columns like this:

CSS

unixtimestamp           unixtimestamp
2015-01-17 12:00:06.000 2015-01-17 12:00:07.000
2015-01-17 12:00:06.000 2015-01-17 12:00:08.000
2015-01-17 12:00:11.000 2015-01-17 12:00:12.000
2015-01-17 12:00:11.000 2015-01-17 12:00:13.000
2015-01-17 12:00:06.000 2015-01-17 12:00:07.000
2015-01-17 12:00:06.000 2015-01-17 12:00:08.000
2015-01-17 12:00:10.000 2015-01-17 12:00:11.000

Posted 17-Jan-15 2:14am

Mohd Arshad Malik

Comments

Wendelius 17-Jan-15 8:27am

Modified the query a bit.

Don't comments to answers as new solutions. Instead, use the "Have a Question or Comment" button.

King Fisher 17-Jan-15 8:28am

Remove this Post and kindly use Comment Widget for your Queries..

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-01-17T02:07:00

Haven't actually tested this but perhaps something like this:

SQL

select start.unixtimestamp,
       stop.unixtimestamp
from table1 start,
     table1 stop
where start.speed        = 0
and   stop.vehicleid     = start.vehicleid
and   stop.unixtimestamp > start.unixtimestamp
and   stop.speed         = 0
and   exists (select 1 
              from  table1 prev
              where prev.vehicleid     = start.vehicleid
              and   prev.unixtimestamp = (select max(p2.unixtimestamp)
                                          from   table1 p2
                                          where  p2.vehicleid     = start.vehicleid
                                          and    p2.unixtimestamp < start.unixtimestamp)
              and   prev.speed > 0) 
and stop.unixtimestamp = (select max(p4.unixtimestamp)
                          from table1 p4
						  where p4.unixtimestamp < (select min(p3.unixtimestamp)
                                 from   table1 p3
                                 where  p3.vehicleid     = stop.vehicleid
                                 and    p3.unixtimestamp > start.unixtimestamp
                                 and    p3.speed         != 0))

EDIT: corrected few mistakes