Home » SQL & PL/SQL » SQL & PL/SQL » Time Between Consecutive Locations (Oracle 12c)
Time Between Consecutive Locations [message #679343] Mon, 24 February 2020 08:57 Go to next message
run400
Messages: 2
Registered: February 2020
Junior Member
Hello, I am currently trying to solve a problem we have determine the time an individual spends in one location. In essence, we have data where a user can travel throughout multiple countries. They can take internal flights and this is where I get my problem. I need to determine how long an individual spends in a location for consecutive periods. In the code below the individual travels from GB to ES. Although they travel internally they basically stay in ES between the 15th Feb to 18th Feb. I'm trying to find a way to calculate this time, so far using lead/lag functions to achieve this. The area where my code below starts to fall over is when we get the same country appearing later in the travel. In this scenario I was the count to start again. So for ES in the example below the user would be in ES between the 15th Feb to 18th Feb and then again between the 21st Feb and 23rd Feb. It's the break point that I'm struggling to get my from a to dates to show correctly.

Any advice greatly appreciated

WITH CTE_sRC AS 
(
select        'ABCDEF' AS Booking_ID, 'GB' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 10 as From_Date, trunc(sysdate) - 9 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'ES' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 9 as From_Date, trunc(sysdate) - 8 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'ES' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 8 as From_Date, trunc(sysdate) - 6 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'ES' as From_Loc, 'HK' as To_Loc, trunc(sysdate) - 6 as From_Date, trunc(sysdate) - 5 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'HK' as From_Loc, 'TW' as To_Loc, trunc(sysdate) - 4 as From_Date, trunc(sysdate) - 3 as Arrive_Date  from dual UNION ALL
select        'ABCDEF' AS Booking_ID, 'TW' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 3 as From_Date, trunc(sysdate) - 3 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'ES' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 2 as From_Date, trunc(sysdate) - 2 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'ES' as From_Loc, 'GB' as To_Loc, trunc(sysdate) - 1 as From_Date, trunc(sysdate) - 1 as Arrive_Date  from dual 
)

SELECT src.*
        , booking_id
        , to_loc || '-' || ld_from as comb_trips
        , case when to_loc = ld_to then 0 else 1 end as chk
        , min(arrive_date) over(partition by booking_id, (to_loc || '-' || ld_from)) as Updated_From_Date
        , max(ld_from_dt) over(partition by booking_id, (to_loc || '-' || ld_from)) as Updated_To_Date
from
(
      SELECT  a.*
              , lead(from_loc) over(partition by a.booking_id order by Arrive_Date) as ld_from
              , lead(from_loc, 2,0) over(partition by a.booking_id order by Arrive_Date) as ld_from2
              , lag(from_loc) over(partition by a.booking_id order by Arrive_Date) as lag_to
              , lead(to_loc) over(partition by a.booking_id order by Arrive_Date) as ld_to
              , lead(arrive_Date) over(partition by a.booking_id order by Arrive_Date) as ld_arrive_dt
              , lead(From_Date) over(partition by a.booking_id order by Arrive_Date) as ld_from_dt
              
      FROM    CTE_SRC a
) src

order by 4
Re: Time Between Consecutive Locations [message #679345 is a reply to message #679343] Mon, 24 February 2020 11:41 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2953
Registered: January 2010
Location: Connecticut, USA
Senior Member
WITH CTE_sRC AS
(
select        'ABCDEF' AS Booking_ID, 'GB' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 10 as From_Date, trunc(sysdate) - 9 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'ES' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 9 as From_Date, trunc(sysdate) - 8 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'ES' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 8 as From_Date, trunc(sysdate) - 6 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'ES' as From_Loc, 'HK' as To_Loc, trunc(sysdate) - 6 as From_Date, trunc(sysdate) - 5 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'HK' as From_Loc, 'TW' as To_Loc, trunc(sysdate) - 4 as From_Date, trunc(sysdate) - 3 as Arrive_Date  from dual UNION ALL
select        'ABCDEF' AS Booking_ID, 'TW' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 3 as From_Date, trunc(sysdate) - 3 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'ES' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 2 as From_Date, trunc(sysdate) - 2 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'ES' as From_Loc, 'GB' as To_Loc, trunc(sysdate) - 1 as From_Date, trunc(sysdate) - 1 as Arrive_Date  from dual
),
t as (
      select  row_number() over(partition by booking_id order by from_date) rn,
              cte_src.*
        from  cte_src
     )
select  connect_by_root arrive_date stay_from,
        from_date stay_to
  from  t
  where connect_by_isleaf = 1
  start with from_loc != 'ES'
         and to_loc = 'ES'
  connect by from_loc = 'ES'
         and booking_id = prior booking_id
         and rn = prior rn + 1
/

STAY_FROM STAY_TO
--------- ---------
15-FEB-20 18-FEB-20
21-FEB-20 23-FEB-20

SQL>
SY.
Re: Time Between Consecutive Locations [message #679360 is a reply to message #679343] Tue, 25 February 2020 08:44 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
Just filter out the rows where the To_loc are the same as from_loc.

WITH src as (
select        'JOHN SMITH' AS TRAVELLER, 'GB' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 10 as START_DATE, trunc(sysdate) - 9 as END_DATE  from dual union all
select        'JOHN SMITH' AS TRAVELLER, 'ES' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 9 as START_DATE, trunc(sysdate) - 8 as END_DATE  from dual union all
select        'JOHN SMITH' AS TRAVELLER, 'ES' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 8 as START_DATE, trunc(sysdate) - 6 as END_DATE  from dual union all
select        'JOHN SMITH' AS TRAVELLER, 'ES' as From_Loc, 'HK' as To_Loc, trunc(sysdate) - 6 as START_DATE, trunc(sysdate) - 5 as END_DATE  from dual union all
select        'JOHN SMITH' AS TRAVELLER, 'HK' as From_Loc, 'TW' as To_Loc, trunc(sysdate) - 4 as START_DATE, trunc(sysdate) - 3 as END_DATE  from dual UNION ALL
select        'JOHN SMITH' AS TRAVELLER, 'TW' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 3 as START_DATE, trunc(sysdate) - 3 as END_DATE  from dual union all
select        'JOHN SMITH' AS TRAVELLER, 'ES' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 2 as START_DATE, trunc(sysdate) - 2 as END_DATE  from dual union all
select        'JOHN SMITH' AS TRAVELLER, 'ES' as From_Loc, 'GB' as To_Loc, trunc(sysdate) - 1 as START_DATE, trunc(sysdate) - 1 as END_DATE  from dual
)
select traveller, from_loc, to_loc, START_DATE, NVL(LEAD(START_DATE) OVER (partition by traveller ORDER BY START_DATE), end_date) end_date
from src
where from_loc <> to_loc;

TRAVELLER  FR TO START_DAT END_DATE
---------- -- -- --------- ---------
JOHN SMITH GB ES 15-FEB-20 19-FEB-20
JOHN SMITH ES HK 19-FEB-20 21-FEB-20
JOHN SMITH HK TW 21-FEB-20 22-FEB-20
JOHN SMITH TW ES 22-FEB-20 24-FEB-20
JOHN SMITH ES GB 24-FEB-20 24-FEB-20

JP

[Updated on: Tue, 25 February 2020 10:07]

Report message to a moderator

Re: Time Between Consecutive Locations [message #679389 is a reply to message #679360] Thu, 27 February 2020 05:02 Go to previous messageGo to next message
run400
Messages: 2
Registered: February 2020
Junior Member
Many thanks to you both for your replies. This helped give me the approach to solving my issue. So both approaches work, but more so on a case by case basis whereas I needed this to cover all our records. I couldn't work out how to make the connect by approach work when not manually entering the start_with locations and I need this to work for several thousand different bookings. On the other approach although excluding the from and to does provide an answer it excludes some of the detail.

I realised from your code SY that the row_number partition was key here to help me differentiate between the multiple stops within the same country and simple legs going from country to country. With this grouping it was then possible to pull out the data in the format I require. I suspect I'm still over-complicating but fortunately the code is working for our test cases.

WITH CTE_SRC AS 
(
select        'ABCDEF' AS Booking_ID, 'GB' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 10 as From_Date, trunc(sysdate) - 9 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'ES' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 9 as From_Date, trunc(sysdate) - 8 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'ES' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 8 as From_Date, trunc(sysdate) - 6 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'ES' as From_Loc, 'HK' as To_Loc, trunc(sysdate) - 6 as From_Date, trunc(sysdate) - 5 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'HK' as From_Loc, 'TW' as To_Loc, trunc(sysdate) - 4 as From_Date, trunc(sysdate) - 3 as Arrive_Date  from dual UNION ALL
select        'ABCDEF' AS Booking_ID, 'TW' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 3 as From_Date, trunc(sysdate) - 3 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'ES' as From_Loc, 'ES' as To_Loc, trunc(sysdate) - 2 as From_Date, trunc(sysdate) - 2 as Arrive_Date  from dual union all
select        'ABCDEF' AS Booking_ID, 'ES' as From_Loc, 'GB' as To_Loc, trunc(sysdate) - 1 as From_Date, trunc(sysdate) - 1 as Arrive_Date  from dual 
)

select
      rnk2
      , rnk3,src.rnk3 - src.rnk2 as ts,
        case when src.recs_to_loc_per_book = 1 then Updated_From_Date
              else min(arrive_date) over(partition by booking_id, (comb_trips), src.rnk3 - src.rnk2) end as Updated_From_Date
        , case when src.recs_to_loc_per_book = 1 then Updated_To_Date
              else max(ld_from_dt) over(partition by booking_id, (comb_trips), src.rnk3 - src.rnk2) end as Updated_To_Date
        , src.*
      
from
      (
        SELECT src.*
                , to_loc || '-' || ld_from as comb_trips
                , min(arrive_date) over(partition by booking_id, (to_loc || '-' || ld_from)) as Updated_From_Date
                , max(ld_from_dt) over(partition by booking_id, (to_loc || '-' || ld_from)) as Updated_To_Date
                , row_number() over(partition by booking_id, (to_loc || '-' || ld_from) order by arrive_date) as rnk2
                , row_number() over(partition by booking_id order by arrive_date) as rnk3
                , count(*) over(partition by To_Loc) as recs_to_loc_per_book
        from
        (
              SELECT  a.*
                      , lead(from_loc) over(partition by a.booking_id order by Arrive_Date) as ld_from
                      , lag(from_loc) over(partition by a.booking_id order by Arrive_Date) as lag_to
                      , lead(to_loc) over(partition by a.booking_id order by Arrive_Date) as ld_to
                      , lead(arrive_Date) over(partition by a.booking_id order by Arrive_Date) as ld_arrive_dt
                      , lead(From_Date) over(partition by a.booking_id order by Arrive_Date) as ld_from_dt
                      
              FROM    CTE_SRC a
        ) src
      ) src
order by from_date
;

Re: Time Between Consecutive Locations [message #679412 is a reply to message #679389] Fri, 28 February 2020 03:21 Go to previous message
_jum
Messages: 570
Registered: February 2008
Senior Member
May be Recursive Subquery Factoring (or MODEL clause) can help to solve the problem. For clarity I added a flight number. It's simple to "parse" the route for stays in the different coumtries. Alterantively one can use different additional columns to add the stop days or nested tables or....
WITH cte_src (bookin_id, flight_nr, from_loc, to_loc, from_Date, arrive_date) AS 
(
  SELECT 'ABCDEF', 4401, 'GB', 'ES', trunc(date '2020-02-24') - 10 as from_Date, trunc(date '2020-02-24') - 9 as Arrive_Date  FROM dual UNION ALL
  SELECT 'ABCDEF', 3401, 'ES', 'ES', trunc(date '2020-02-24') -  9 as from_Date, trunc(date '2020-02-24') - 8 as Arrive_Date  FROM dual UNION ALL
  SELECT 'ABCDEF', 3402, 'ES', 'ES', trunc(date '2020-02-24') -  8 as from_Date, trunc(date '2020-02-24') - 6 as Arrive_Date  FROM dual UNION ALL
  SELECT 'ABCDEF', 3403, 'ES', 'HK', trunc(date '2020-02-24') -  6 as from_Date, trunc(date '2020-02-24') - 5 as Arrive_Date  FROM dual UNION ALL
  SELECT 'ABCDEF', 8521, 'HK', 'TW', trunc(date '2020-02-24') -  4 as from_Date, trunc(date '2020-02-24') - 3 as Arrive_Date  FROM dual UNION ALL
  SELECT 'ABCDEF', 8861, 'TW', 'ES', trunc(date '2020-02-24') -  3 as from_Date, trunc(date '2020-02-24') - 3 as Arrive_Date  FROM dual UNION ALL
  SELECT 'ABCDEF', 3404, 'ES', 'ES', trunc(date '2020-02-24') -  2 as from_Date, trunc(date '2020-02-24') - 2 as Arrive_Date  FROM dual UNION ALL
  SELECT 'ABCDEF', 3405, 'ES', 'GB', trunc(date '2020-02-24') -  1 as from_Date, trunc(date '2020-02-24') - 1 as Arrive_Date  FROM dual 
),
cte ( start_loc, flight_nr, from_loc, to_loc, from_Date, arrive_date, diff, route, lvl) AS
  (SELECT from_loc, flight_nr, from_loc, to_loc, from_Date, arrive_date, NULL, CAST(from_loc||'->'||to_loc AS VARCHAR2(4000)), 1
     FROM cte_src 
    --start condition
    WHERE from_loc != to_loc
      AND from_Date = date '2020-02-14'  
  UNION ALL
   SELECT cte.start_loc, s.flight_nr, s.from_loc, s.to_loc, s.from_Date, s.arrive_date
        , trunc(s.from_Date-cte.arrive_date), route||'['||trunc(s.from_Date-cte.arrive_date)||','||s.flight_nr||'] ->'||s.to_loc, lvl+1
     FROM cte 
     JOIN cte_src s 
       --join condition
       ON  (cte.to_loc=s.from_loc
        AND cte.arrive_date <= s.from_Date)     
   )
  --cycle is the same flight twice
  CYCLE flight_nr SET cyclemark TO 'x' DEFAULT '-'
 SELECT c.*
   FROM cte c 
  WHERE cyclemark != 'x' 
  ORDER BY lvl, diff;

START_LOC FLIGHT_NR FROM_LOC TO_LOC FROM_DATE ARRIVE_DATE DIFF LVL ROUTE CYCLEMARK
GB 4401 GB ES 14/02/2020 15/02/2020 1 GB->ES -
GB 3401 ES ES 15/02/2020 16/02/2020 0 2 GB->ES[0,3401]->ES -
GB 3402 ES ES 16/02/2020 18/02/2020 1 2 GB->ES[1,3402]->ES -
GB 3403 ES HK 18/02/2020 19/02/2020 3 2 GB->ES[3,3403]->HK -
GB 3404 ES ES 22/02/2020 22/02/2020 7 2 GB->ES[7,3404]->ES -
GB 3405 ES GB 23/02/2020 23/02/2020 8 2 GB->ES[8,3405]->GB -
GB 3402 ES ES 16/02/2020 18/02/2020 0 3 GB->ES[0,3401]->ES[0,3402]->ES -
GB 3403 ES HK 18/02/2020 19/02/2020 0 3 GB->ES[1,3402]->ES[0,3403]->HK -
GB 8521 HK TW 20/02/2020 21/02/2020 1 3 GB->ES[3,3403]->HK[1,8521]->TW -
GB 3405 ES GB 23/02/2020 23/02/2020 1 3 GB->ES[7,3404]->ES[1,3405]->GB -
GB 3403 ES HK 18/02/2020 19/02/2020 2 3 GB->ES[0,3401]->ES[2,3403]->HK -
GB 3404 ES ES 22/02/2020 22/02/2020 4 3 GB->ES[1,3402]->ES[4,3404]->ES -
GB 3405 ES GB 23/02/2020 23/02/2020 5 3 GB->ES[1,3402]->ES[5,3405]->GB -
GB 3404 ES ES 22/02/2020 22/02/2020 6 3 GB->ES[0,3401]->ES[6,3404]->ES -
GB 3405 ES GB 23/02/2020 23/02/2020 7 3 GB->ES[0,3401]->ES[7,3405]->GB -
GB 3403 ES HK 18/02/2020 19/02/2020 0 4 GB->ES[0,3401]->ES[0,3402]->ES[0,3403]->HK -
GB 8861 TW ES 21/02/2020 21/02/2020 0 4 GB->ES[3,3403]->HK[1,8521]->TW[0,8861]->ES -
GB 3405 ES GB 23/02/2020 23/02/2020 1 4 GB->ES[0,3401]->ES[6,3404]->ES[1,3405]->GB -
GB 8521 HK TW 20/02/2020 21/02/2020 1 4 GB->ES[1,3402]->ES[0,3403]->HK[1,8521]->TW -
GB 3405 ES GB 23/02/2020 23/02/2020 1 4 GB->ES[1,3402]->ES[4,3404]->ES[1,3405]->GB -
GB 8521 HK TW 20/02/2020 21/02/2020 1 4 GB->ES[0,3401]->ES[2,3403]->HK[1,8521]->TW -
GB 3404 ES ES 22/02/2020 22/02/2020 4 4 GB->ES[0,3401]->ES[0,3402]->ES[4,3404]->ES -
GB 3405 ES GB 23/02/2020 23/02/2020 5 4 GB->ES[0,3401]->ES[0,3402]->ES[5,3405]->GB -
GB 8861 TW ES 21/02/2020 21/02/2020 0 5 GB->ES[1,3402]->ES[0,3403]->HK[1,8521]->TW[0,8861]->ES -
GB 8861 TW ES 21/02/2020 21/02/2020 0 5 GB->ES[0,3401]->ES[2,3403]->HK[1,8521]->TW[0,8861]->ES -
GB 3404 ES ES 22/02/2020 22/02/2020 1 5 GB->ES[3,3403]->HK[1,8521]->TW[0,8861]->ES[1,3404]->ES -
GB 3405 ES GB 23/02/2020 23/02/2020 1 5 GB->ES[0,3401]->ES[0,3402]->ES[4,3404]->ES[1,3405]->GB -
GB 8521 HK TW 20/02/2020 21/02/2020 1 5 GB->ES[0,3401]->ES[0,3402]->ES[0,3403]->HK[1,8521]->TW -
GB 3405 ES GB 23/02/2020 23/02/2020 2 5 GB->ES[3,3403]->HK[1,8521]->TW[0,8861]->ES[2,3405]->GB -
GB 8861 TW ES 21/02/2020 21/02/2020 0 6 GB->ES[0,3401]->ES[0,3402]->ES[0,3403]->HK[1,8521]->TW[0,8861]->ES -
GB 3404 ES ES 22/02/2020 22/02/2020 1 6 GB->ES[0,3401]->ES[2,3403]->HK[1,8521]->TW[0,8861]->ES[1,3404]->ES -
GB 3405 ES GB 23/02/2020 23/02/2020 1 6 GB->ES[3,3403]->HK[1,8521]->TW[0,8861]->ES[1,3404]->ES[1,3405]->GB -
GB 3404 ES ES 22/02/2020 22/02/2020 1 6 GB->ES[1,3402]->ES[0,3403]->HK[1,8521]->TW[0,8861]->ES[1,3404]->ES -
GB 3405 ES GB 23/02/2020 23/02/2020 2 6 GB->ES[1,3402]->ES[0,3403]->HK[1,8521]->TW[0,8861]->ES[2,3405]->GB -
GB 3405 ES GB 23/02/2020 23/02/2020 2 6 GB->ES[0,3401]->ES[2,3403]->HK[1,8521]->TW[0,8861]->ES[2,3405]->GB -
GB 3405 ES GB 23/02/2020 23/02/2020 1 7 GB->ES[0,3401]->ES[2,3403]->HK[1,8521]->TW[0,8861]->ES[1,3404]->ES[1,3405]->GB -
GB 3405 ES GB 23/02/2020 23/02/2020 1 7 GB->ES[1,3402]->ES[0,3403]->HK[1,8521]->TW[0,8861]->ES[1,3404]->ES[1,3405]->GB -
GB 3404 ES ES 22/02/2020 22/02/2020 1 7 GB->ES[0,3401]->ES[0,3402]->ES[0,3403]->HK[1,8521]->TW[0,8861]->ES[1,3404]->ES -
GB 3405 ES GB 23/02/2020 23/02/2020 2 7 GB->ES[0,3401]->ES[0,3402]->ES[0,3403]->HK[1,8521]->TW[0,8861]->ES[2,3405]->GB -
GB 3405 ES GB 23/02/2020 23/02/2020 1 8 GB->ES[0,3401]->ES[0,3402]->ES[0,3403]->HK[1,8521]->TW[0,8861]->ES[1,3404]->ES[1,3405]->GB -


[Updated on: Fri, 28 February 2020 03:25]

Report message to a moderator

Previous Topic: Oracle 11g Sequence(merged)
Next Topic: query-assistance
Goto Forum:
  


Current Time: Thu Jun 04 16:49:20 CDT 2020