CODEWITHSUNDEEP
sqlsql-server
Ahmed M. Fathy
Ahmed M. Fathy

Reputation: 28

Use SQL Group By or Window Function to analyse rows in time sequence?

I have a table with data like the following:

+-----+--------+-------+--------+---------+----------------------+
| ID  |  Name  |  Lat  |  Long  |  Speed  |       DateTime       |
+-----+--------+-------+--------+---------+----------------------+
|  1  |  Test  |   121 |  2131  |      0  |  10-11-2018T12:00:00 |
|  1  |  Test  |   121 |  2131  |    100  |  10-11-2018T12:10:00 |
|  1  |  Test  |   121 |  2131  |      0  |  10-11-2018T01:00:00 |
|  1  |  Test  |   121 |  2131  |    102  |  10-11-2018T01:10:00 |
+-----+--------+-------+--------+---------+----------------------+

I need to get:

+---------+-----------------------+---------------------+
|         |         from          |         to          |
+---------+-----------------------+---------------------+
| stop    |  10-11-2018T12:00:00  | 10-11-2018T12:10:00 |
| moving  |  10-11-2018T12:10:00  | 10-11-2018T01:00:00 |
| stop    |  10-11-2018T01:00:00  | 10-11-2018T01:10:00 |
| moving  |  10-11-2018T01:10:00  | null                |
+---------+-----------------------+---------------------+

Upvotes: 1

Views: 24

Answers (2)

D-Shih
D-Shih

Reputation: 46239

You can try to use ROW_NUMBER with CASE WHEN and lead window function to make it.

select CASE WHEN ROW_NUMBER() over( order by (SELECT NULL)) % 2 = 0 THEN 'Move' ELSE 'Stop' END,
       [DateTime] 'from',
       lead([DateTime]) over(order by (SELECT NULL)) 'to'  
from t

sqlfiddle

But I think you might want to use order by DateTime

select CASE WHEN ROW_NUMBER() over(partition by ID order by DateTime) % 2 = 0 THEN 'Move' ELSE 'Stop' END,
      DateTime 'from',
      lead(DateTime) over(partition by ID order by DateTime) 'to'   
from t

Upvotes: 1

Neil
Neil

Reputation: 1633

You can join the table on itself using ID and adjacent DateTime values. This considers the scenario where rows aren't always alternating between stopped and moving.

SELECT
    CASE WHEN t1.Speed <> 0 THEN 'moving'
    ELSE 'stop' END as 'movingStatus',
    t1.DateTime as 'from',
    t2.DateTime as 'to'
FROM MyTable t1, MyTable t2
WHERE
    t1.ID = t2.ID
    t2.DateTime = (SELECT MIN(DateTime) FROM MyTable WHERE DateTime > t1.DateTime)

Upvotes: 0

Related Questions