HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Get start time and end time of a trip in SQL Server

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sqlsql-serverstackoverflowsql-server-2012dba
serversqlstarttimegettripendand

Problem

I have a table that contains zone in and out information about a bus, and its structure is like this:

id zoneid status datetime
1 35 IN 2016-02-02 11:36:39
2 35 OUT 2016-02-02 11:36:59
3 36 IN 2016-02-02 11:37:35
4 36 IN 2016-02-02 11:37:49
5 36 OUT 2016-02-02 11:38:06
6 38 IN 2016-02-02 11:39:37
7 38 OUT 2016-02-02 11:39:55
8 38 OUT 2016-02-02 11:40:09
9 36 IN 2016-02-02 11:41:39
10 36 OUT 2016-02-02 11:41:49
11 35 IN 2016-02-02 11:42:01
12 35 IN 2016-02-02 11:42:13
13 35 IN 2016-02-02 11:42:28
14 35 OUT 2016-02-02 11:42:39
15 36 IN 2016-02-02 11:42:49
16 36 IN 2016-02-02 11:43:03
17 36 OUT 2016-02-02 11:43:19
18 38 IN 2016-02-02 11:44:00
19 38 OUT 2016-02-02 11:44:18
20 38 OUT 2016-02-02 11:44:39
21 36 IN 2016-02-02 11:45:20
22 36 OUT 2016-02-02 11:45:43
23 35 IN 2016-02-02 11:46:00
24 35 IN 2016-02-02 11:46:04

Zone 35 and 38 are the edge point of a trip. When a bus gets out from zone 35, first trip starts, and when it enters zone 38, first trip ends. After that, whenever it gets out of zone 38, second trip starts, and when it enters zone 35, second trip ends. This process goes on. What I actually need is to get the start time and end time of a trip. According to the data in table, the result should be:

starttime endtime
2016-02-02 11:36:59 2016-02-02 11:39:37
2016-02-02 11:39:55 2016-02-02 11:42:01
2016-02-02 11:42:39 2016-02-02 11:44:00
2016-02-02 11:44:18 2016-02-02 11:46:00


Any suggestions? Thanks in advance.

Solution

My idea for this is ,first, to filter only the records with zoneID 35 or 38 that have have status = OUT (this mean the start of the trip)

Then base on this, give me the next record(most near the the record as datetime)that have different zoneID and different status
(Ex:If I have 35 and OUT , give me the next 38 and IN.)

And I added a additional condition.The previous zoneID (LAG) of the current zoneID are different 

DECLARE @Table1 TABLE
    ([id] int, [zoneid] int, [status] varchar(3), [datetime] datetime);

INSERT INTO @Table1
    ([id], [zoneid], [status], [datetime])
VALUES
    (1, 35, 'IN', '2016-02-02 11:36:39'),
    (2, 35, 'OUT', '2016-02-02 11:36:59'),
    (3, 36, 'IN', '2016-02-02 11:37:35'),
    (4, 36, 'IN', '2016-02-02 11:37:49'),
    (5, 36, 'OUT', '2016-02-02 11:38:06'),
    (6, 38, 'IN', '2016-02-02 11:39:37'),
    (7, 38, 'OUT', '2016-02-02 11:39:55'),
    (8, 38, 'OUT', '2016-02-02 11:40:09'),
    (9, 36, 'IN', '2016-02-02 11:41:39'),
    (10, 36, 'OUT', '2016-02-02 11:41:49'),
    (11, 35, 'IN', '2016-02-02 11:42:01'),
    (12, 35, 'IN', '2016-02-02 11:42:13'),
    (13, 35, 'IN', '2016-02-02 11:42:28'),
    (14, 35, 'OUT', '2016-02-02 11:42:39'),
    (15, 36, 'IN', '2016-02-02 11:42:49'),
    (16, 36, 'IN', '2016-02-02 11:43:03'),
    (17, 36, 'OUT', '2016-02-02 11:43:19'),
    (18, 38, 'IN', '2016-02-02 11:44:00'),
    (19, 38, 'OUT', '2016-02-02 11:44:18'),
    (20, 38, 'OUT', '2016-02-02 11:44:39'),
    (21, 36, 'IN', '2016-02-02 11:45:20'),
    (22, 36, 'OUT', '2016-02-02 11:45:43'),
    (23, 35, 'IN', '2016-02-02 11:46:00'),
    (24, 35, 'IN', '2016-02-02 11:46:04');

SELECT
    [id]
    ,starttime
    ,endtime
FROM
(
    SELECT
        T1.[id]
        ,T1.[zoneid]
        ,T1.[status]
        ,T1.[datetime] AS starttime         
        ,CA.[datetime] AS endtime
        ,LAG(T1.[zoneid],1,1) OVER(ORDER BY T1.[id]) AS PrevZoneID
    FROM
        @Table1 AS T1
        CROSS APPLY
        (
            SELECT TOP(1)
                T2.[id], T2.[zoneid], T2.[status], T2.[datetime]
            FROM
                @Table1 AS T2
            WHERE       
                T1.[zoneid] <> T2.[zoneid] --give the other zone
                AND T1.[status] <> T2.[status] -- different status
                AND (T2.[zoneid] = 35 OR T2.[zoneid] = 38)
                AND T1.[datetime]  A.[zoneid];


I have the next output:

id          starttime               endtime    
2           2016-02-02 11:36:59.000 2016-02-02 11:39:37.000
7           2016-02-02 11:39:55.000 2016-02-02 11:42:01.000
14          2016-02-02 11:42:39.000 2016-02-02 11:44:00.000
19          2016-02-02 11:44:18.000 2016-02-02 11:46:00.000

Code Snippets

DECLARE @Table1 TABLE
    ([id] int, [zoneid] int, [status] varchar(3), [datetime] datetime);

INSERT INTO @Table1
    ([id], [zoneid], [status], [datetime])
VALUES
    (1, 35, 'IN', '2016-02-02 11:36:39'),
    (2, 35, 'OUT', '2016-02-02 11:36:59'),
    (3, 36, 'IN', '2016-02-02 11:37:35'),
    (4, 36, 'IN', '2016-02-02 11:37:49'),
    (5, 36, 'OUT', '2016-02-02 11:38:06'),
    (6, 38, 'IN', '2016-02-02 11:39:37'),
    (7, 38, 'OUT', '2016-02-02 11:39:55'),
    (8, 38, 'OUT', '2016-02-02 11:40:09'),
    (9, 36, 'IN', '2016-02-02 11:41:39'),
    (10, 36, 'OUT', '2016-02-02 11:41:49'),
    (11, 35, 'IN', '2016-02-02 11:42:01'),
    (12, 35, 'IN', '2016-02-02 11:42:13'),
    (13, 35, 'IN', '2016-02-02 11:42:28'),
    (14, 35, 'OUT', '2016-02-02 11:42:39'),
    (15, 36, 'IN', '2016-02-02 11:42:49'),
    (16, 36, 'IN', '2016-02-02 11:43:03'),
    (17, 36, 'OUT', '2016-02-02 11:43:19'),
    (18, 38, 'IN', '2016-02-02 11:44:00'),
    (19, 38, 'OUT', '2016-02-02 11:44:18'),
    (20, 38, 'OUT', '2016-02-02 11:44:39'),
    (21, 36, 'IN', '2016-02-02 11:45:20'),
    (22, 36, 'OUT', '2016-02-02 11:45:43'),
    (23, 35, 'IN', '2016-02-02 11:46:00'),
    (24, 35, 'IN', '2016-02-02 11:46:04');


SELECT
    [id]
    ,starttime
    ,endtime
FROM
(
    SELECT
        T1.[id]
        ,T1.[zoneid]
        ,T1.[status]
        ,T1.[datetime] AS starttime         
        ,CA.[datetime] AS endtime
        ,LAG(T1.[zoneid],1,1) OVER(ORDER BY T1.[id]) AS PrevZoneID
    FROM
        @Table1 AS T1
        CROSS APPLY
        (
            SELECT TOP(1)
                T2.[id], T2.[zoneid], T2.[status], T2.[datetime]
            FROM
                @Table1 AS T2
            WHERE       
                T1.[zoneid] <> T2.[zoneid] --give the other zone
                AND T1.[status] <> T2.[status] -- different status
                AND (T2.[zoneid] = 35 OR T2.[zoneid] = 38)
                AND T1.[datetime] < T2.[datetime]
            ORDER BY 
                T2.[datetime] ASC    
        )CA
    WHERE
        (T1.[zoneid] = 35 AND T1.[status] = 'OUT')
        OR (T1.[zoneid] = 38 AND T1.[status] = 'OUT')
)A
WHERE
    A.PrevZoneID <> A.[zoneid];
id          starttime               endtime    
2           2016-02-02 11:36:59.000 2016-02-02 11:39:37.000
7           2016-02-02 11:39:55.000 2016-02-02 11:42:01.000
14          2016-02-02 11:42:39.000 2016-02-02 11:44:00.000
19          2016-02-02 11:44:18.000 2016-02-02 11:46:00.000

Context

StackExchange Database Administrators Q#128106, answer score: 2

Revisions (0)

No revisions yet.