Hello Tom,
we have a table with appointments. There is a appointment every 5 minutes like this an there may be holes because some appointments are already taken
create table AM_HILFSTABELLE
(
amht_num1 NUMBER,
amht_dat1 DATE
);
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 1, to_date('08.06.2020 08:00:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 2, to_date('08.06.2020 08:05:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 3, to_date('08.06.2020 08:10:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 4, to_date('08.06.2020 08:15:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 5, to_date('08.06.2020 08:20:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 6, to_date('08.06.2020 08:25:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 7, to_date('08.06.2020 08:35:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 8, to_date('08.06.2020 08:40:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 9, to_date('08.06.2020 08:45:00','DD.MM.YYYY HH24:MI:SS'));
insert into am_hilfstabelle(amht_num1,amht_dat1) values ( 110, to_date('08.06.2020 08:50:00','DD.MM.YYYY HH24:MI:SS'));
We want to show the user only appointments that are at least 15 minutes one from the other
In this case
08:00
08:15:
08:35 ( 08:30 is already taken, so 20 min from the last)
08:50 ( 15 minutes from the last one)
We write this appointments in a temporary table that we show the user.
Ww solved it with a cursor and a loop.
Is it possible to do this with a select without the loop so we could avoid the pl/sql overhead and show the result directly to the user
Regards
Andreas
Thanks for your patience. I lost track of this one and discovered it today ... sorry about that.
First I'll extract the minutes passed for each row
SQL> select a.*,
2 nvl(amht_dat1 - lag(amht_dat1) over ( order by amht_num1),0)*1440 mins
3 from am_hilfstabelle a;
AMHT_NUM1 AMHT_DAT1 MINS
---------- ------------------- ----------
1 08/06/2020 08:00:00 0
2 08/06/2020 08:05:00 5
3 08/06/2020 08:10:00 5
4 08/06/2020 08:15:00 5
5 08/06/2020 08:20:00 5
6 08/06/2020 08:25:00 5
7 08/06/2020 08:35:00 10
8 08/06/2020 08:40:00 5
9 08/06/2020 08:45:00 5
110 08/06/2020 08:50:00 5
10 rows selected.
Now I can use those mins as a running total that resets when it gets to 15mins. In 12c, a match_recognize is perfect for that, but on 11.2, we can use MODEL (not as friendly)
SQL> with t as
2 (
3 select a.*,
4 nvl(amht_dat1 - lag(amht_dat1) over ( order by amht_num1),0)*1440 mins
5 from am_hilfstabelle a
6 )
7 select s, e, d, mins, sm from t
8 model dimension by(row_number() over(order by amht_num1) rn)
9 measures(amht_dat1 d, amht_num1 s, amht_num1 e, mins, mins sm)
10 rules(sm[rn > 1] =
11 case when (sm[cv() - 1] + mins[cv()]) > 15 or mins[cv()] > 15
12 then 0 -- mins[cv()]
13 else sm[cv() - 1] + mins[cv()]
14 end,
15 s[rn > 1] =
16 case when(sm[cv() - 1] + mins[cv()]) > 15 or mins[cv()] > 15
17 then s[cv()]
18 else s[cv() - 1]
19 end);
S E D MINS SM
---------- ---------- ------------------- ---------- ----------
1 1 08/06/2020 08:00:00 0 0
1 2 08/06/2020 08:05:00 5 5
1 3 08/06/2020 08:10:00 5 10
1 4 08/06/2020 08:15:00 5 15
5 5 08/06/2020 08:20:00 5 0
5 6 08/06/2020 08:25:00 5 5
5 7 08/06/2020 08:35:00 10 15
8 8 08/06/2020 08:40:00 5 0
8 9 08/06/2020 08:45:00 5 5
8 110 08/06/2020 08:50:00 5 10
10 rows selected.
You can see the logical groupings form.... "S" (being start amht_num1) runs for 4 timeslots (rows), then we switch to S=5 for 2 slots, and so on. Now can group on that
SQL> with t as
2 (
3 select a.*,
4 nvl(amht_dat1 - lag(amht_dat1) over ( order by amht_num1),0)*1440 mins
5 from am_hilfstabelle a
6 )
7 select s, min(d), max(e), max(sm)
8 from (
9 select s, e, d, mins, sm from t
10 model dimension by(row_number() over(order by amht_num1) rn)
11 measures(amht_dat1 d, amht_num1 s, amht_num1 e, mins, mins sm)
12 rules(sm[rn > 1] =
13 case when (sm[cv() - 1] + mins[cv()]) > 15 or mins[cv()] > 15
14 then 0 -- mins[cv()]
15 else sm[cv() - 1] + mins[cv()]
16 end,
17 s[rn > 1] =
18 case when(sm[cv() - 1] + mins[cv()]) > 15 or mins[cv()] > 15
19 then s[cv()]
20 else s[cv() - 1]
21 end)
22 )
23 group by s order by s;
S MIN(D) MAX(E) MAX(SM)
---------- ------------------- ---------- ----------
1 08/06/2020 08:00:00 4 15
5 08/06/2020 08:20:00 7 15
8 08/06/2020 08:40:00 110 10
SQL>