Skip to Main Content
  • Questions
  • select records with certain "distance" to others

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Andreas.

Asked: June 10, 2020 - 5:20 am UTC

Answered by: Connor McDonald - Last updated: August 13, 2020 - 3:52 am UTC

Category: SQL - Version: 11.2

Viewed 1000+ times

You Asked

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

and we said...

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>

and you rated our response

  (1 rating)

Reviews

August 17, 2020 - 6:27 am UTC

Reviewer: A reader


More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.