3

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.

Improve this question
1
  • 3
    It'll be great if you show us what you have tried. That way you may be more likely to learn something new rather than just get another proverbial fish for a day. – Andriy M Feb 3 '16 at 7:28
2

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] < 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];

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
Improve this answer
0
1

Here's a version that accepts any number of trip/route definitions and pulls all the answers at once. Aside from the join to the routes which should perform well with the right indexes, all the trip start and stop calculations are done without joins to a physical table (only using aggregates or constant scan joins). It additionally shows the time waiting at the station (or whatever you want to call waiting between trips).

WITH BusRoutes AS ( -- The bus routes or trips you want, easily defined
   SELECT *
   FROM (VALUES
      ('Line 1', 'Northbound', 35, 38),
      ('Line 1', 'Southbound', 38, 35)
   ) t (Line, Direction, FromZoneId, ToZoneId)
), Groups AS (
   SELECT
      br.FromZoneId,
      br.ToZoneId,
      Grp =
         Row_Number() OVER (ORDER BY bzs.StatusDateTime)
         - Row_Number() OVER (PARTITION BY br.FromZoneId, br.ToZoneId, bzs.ZoneId
            ORDER BY bzs.StatusDateTime),
      bzs.*
   FROM
      BusRoutes br
      CROSS APPLY (VALUES
         ('IN', br.ToZoneId),
         ('OUT', br.FromZoneId)
      ) s ([Status], ZoneId)
      INNER JOIN BusZoneStatuses bzs
         ON s.ZoneId = bzs.ZoneId
         AND s.[Status] = bzs.[Status]
), Ranges AS (
   SELECT
      g.FromZoneId,
      g.ToZoneId,
      TripId = Row_Number() OVER (PARTITION BY g.FromZoneId, g.ToZoneId
         ORDER BY Min(g.StatusDateTime), X.Num) / 2,
      StatusDateTime = Min(g.StatusDateTime),
      g.[Status],
      X.Num
   FROM
      Groups g
      CROSS JOIN (VALUES (1), (2)) x (Num)
   GROUP BY
      g.FromZoneId,
      g.ToZoneId,
      g.[Status],
      g.Grp,
      X.Num
), Result AS (
   SELECT
      FromZoneId,
      ToZoneId,
      FromTime = Min(r.StatusDateTime),
      ToTime = CASE WHEN Count(*) = 1 THEN NULL ELSE Max(r.StatusDateTime) END,
      [Status] = Coalesce(
         Min(CASE WHEN r.Num = 2 THEN r.[Status] ELSE NULL END),
         Min(r.[Status])
      )
   FROM Ranges r
   WHERE TripId > 0
   GROUP BY
      FromZoneId,
      ToZoneId,
      TripId
)
SELECT
   br.Line,
   br.Direction,
   FromZoneId = CASE WHEN a.[Status] = 'IN' THEN NULL ELSE br.FromZoneId END,
   br.ToZoneId,
   a.Activity,
   r.FromTime,
   r.ToTime
FROM
   BusRoutes br
   INNER JOIN Result r
      ON br.FromZoneId = r.FromZoneId
      AND br.ToZoneId = r.ToZoneId
   INNER JOIN (VALUES
      ('IN', 'At station'),
      ('OUT', 'On trip')
   ) a ([Status], Activity)
      ON r.[Status] = a.[Status]
ORDER BY
   br.Line,
   a.Activity,
   r.FromTime
;

The output:

Line   Direction  FromZone ToZone Activity FromTime                ToTime
------ ---------- -------- ------ -------- ----------------------- -----------------------
Line 1 Southbound NULL     35     Station  2016-02-02 11:36:39.000 2016-02-02 11:39:55.000
Line 1 Northbound NULL     38     Station  2016-02-02 11:39:37.000 2016-02-02 11:42:39.000
Line 1 Southbound NULL     35     Station  2016-02-02 11:42:01.000 2016-02-02 11:44:18.000
Line 1 Northbound NULL     38     Station  2016-02-02 11:44:00.000 NULL
Line 1 Southbound NULL     35     Station  2016-02-02 11:46:00.000 NULL
Line 1 Northbound 35       38     On trip  2016-02-02 11:36:59.000 2016-02-02 11:39:37.000
Line 1 Southbound 38       35     On trip  2016-02-02 11:39:55.000 2016-02-02 11:42:01.000
Line 1 Northbound 35       38     On trip  2016-02-02 11:42:39.000 2016-02-02 11:44:00.000
Line 1 Southbound 38       35     On trip  2016-02-02 11:44:18.000 2016-02-02 11:46:00.000

Here's the setup data I used, in case anyone wants to play with it.

CREATE TABLE dbo.BusZoneStatuses (
   Id int NOT NULL,
   ZoneId int NOT NULL,
   [Status] varchar(3) NOT NULL,
   StatusDateTime datetime NOT NULL
);

INSERT dbo.BusZoneStatuses (Id, ZoneId, Status, StatusDateTime)
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')
;

Anyone who would like an explanation for what's going on here can find a detailed explanation of the technique at this similar question/answer on StackOverflow.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.