So the rule is:
- Find the number of minutes between flights from the same destination
- When the running total of these is greater than 100, start the total from zero again?
If so, I'm not following why the final Rome flight has a value of 1320. Surely this should be zero, because you've gone over the 100 limit?
Anyway, here's one way to do it:
- Assign a row number to each destination, ordered by date
- Start with row = 1 for each destination
- Recursively work down the results. Each time increment the running total, provided it's <= 100. Otherwise start again from zero
I've done this with recursive subquery factoring (CTEs):
create table t (Flight_Date date, Destination varchar2(6), DateDiffMin int);
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
insert into t values ('2016-06-08 16:30:00', 'Berlin', 0);
insert into t values ('2016-06-08 17:30:00', 'Berlin', 60);
insert into t values ('2016-06-08 18:00:00', 'Berlin', 30);
insert into t values ('2016-06-08 18:10:00', 'Berlin', 10);
insert into t values ('2016-06-09 02:30:00', 'Berlin', 500);
insert into t values ('2016-06-09 02:50:00', 'Berlin', 20);
insert into t values ('2016-06-09 03:50:00', 'Berlin', 60);
insert into t values ('2016-06-09 04:50:00', 'Berlin', 60);
insert into t values ('2016-06-09 04:55:00', 'Berlin', 5);
insert into t values ('2016-06-09 05:50:00', 'Berlin', 55);
insert into t values ('2016-06-13 07:20:00', 'Rome', 0);
insert into t values ('2016-06-13 08:50:00', 'Rome', 90);
insert into t values ('2016-06-13 09:20:00', 'Rome', 30);
insert into t values ('2016-06-14 07:20:00', 'Rome', 1320);
with rws as (
select t.*,
row_number() over (partition by destination order by flight_date) rn
from t
), tots (rn, flight_date, dest, DateDiffMin, tot) as (
select rn, flight_date, destination, DateDiffMin, 0 from rws
where rn = 1
union all
select r.rn, r.flight_date, r.destination, r.DateDiffMin,
case when t.tot + r.DateDiffMin > 100 then
0 else t.tot + r.DateDiffMin
end
from tots t
join rws r
on r.rn = t.rn + 1
and t.dest = r.destination
)
select * from tots
order by flight_date, dest;
RN FLIGHT_DATE DEST DATEDIFFMIN TOT
1 2016-06-08 16:30:00 Berlin 0 0
2 2016-06-08 17:30:00 Berlin 60 60
3 2016-06-08 18:00:00 Berlin 30 90
4 2016-06-08 18:10:00 Berlin 10 100
5 2016-06-09 02:30:00 Berlin 500 0
6 2016-06-09 02:50:00 Berlin 20 20
7 2016-06-09 03:50:00 Berlin 60 80
8 2016-06-09 04:50:00 Berlin 60 0
9 2016-06-09 04:55:00 Berlin 5 5
10 2016-06-09 05:50:00 Berlin 55 60
1 2016-06-13 07:20:00 Rome 0 0
2 2016-06-13 08:50:00 Rome 90 90
3 2016-06-13 09:20:00 Rome 30 0
4 2016-06-14 07:20:00 Rome 1,320 0
If you want to reset at a different duration, e.g. 60 minutes, just change the condition in the case.
Note you don't need to store the time difference! You could calculate it in the first with clause alongside the rownum using lag:
select t.*,
row_number() over (partition by destination order by flight_date) rn,
round((flight_date - lag(flight_date, 1, flight_date) over (
partition by destination order by flight_date)
) * 1440) lg
from t;
FLIGHT_DATE DESTINATION DATEDIFFMIN RN LG
2016-06-08 16:30:00 Berlin 0 1 0
2016-06-08 17:30:00 Berlin 60 2 60
2016-06-08 18:00:00 Berlin 30 3 30
2016-06-08 18:10:00 Berlin 10 4 10
2016-06-09 02:30:00 Berlin 500 5 500
2016-06-09 02:50:00 Berlin 20 6 20
2016-06-09 03:50:00 Berlin 60 7 60
2016-06-09 04:50:00 Berlin 60 8 60
2016-06-09 04:55:00 Berlin 5 9 5
2016-06-09 05:50:00 Berlin 55 10 55
2016-06-13 07:20:00 Rome 0 1 0
2016-06-13 08:50:00 Rome 90 2 90
2016-06-13 09:20:00 Rome 30 3 30
2016-06-14 07:20:00 Rome 1,320 4 1,320