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

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

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.