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 |
|
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 |
Solomon Yakobson
Messages: 3275 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 |
|
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 |
|
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 |
_jum
Messages: 577 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
|
|
|
Goto Forum:
Current Time: Mon Jun 17 00:49:45 CDT 2024
|