I have branch timing view as follows. Date will change based on sysdate
TIME_FROM TIME_TO
09/08/2020 07:00:00 AM 09/08/2020 02:00:00 PM
09/08/2020 04:00:00 PM 09/08/2020 06:00:00 PM
I want to generate appointment slots with 60 minutes duration like the following. 60 minutes is variable and i will pass it as parameter. I want to get the query result like this
7.00 AM
8.00 AM
9.00 AM
10.00 AM
11.00 AM
12.00 PM
1.00 PM
4.00 PM
5.00 PM
Exclude shift ending times( 2.00 PM and 06:00 PM) as no point in including them
Another, non-recursive CTE approach, might be
SQL> with test (time_from, time_to) as
2 (select to_date('09.08.2020 07:00', 'dd.mm.yyyy hh24:mi'),
3 to_date('09.08.2020 14:00', 'dd.mm.yyyy hh24:mi')
4 from dual union all
5 select to_date('09.08.2020 16:00', 'dd.mm.yyyy hh24:mi'),
6 to_date('09.08.2020 18:00', 'dd.mm.yyyy hh24:mi')
7 from dual
8 )
9 select time_from + ((column_value - 1) * 60) / (24 * 60) time
10 from test cross join
11 table(cast(multiset(select level from dual
12 connect by level <= (time_to - time_from) * 24
13 ) as sys.odcinumberlist));
TIME
----------------
09.08.2020 07:00
09.08.2020 08:00
09.08.2020 09:00
09.08.2020 10:00
09.08.2020 11:00
09.08.2020 12:00
09.08.2020 13:00
09.08.2020 16:00
09.08.2020 17:00
9 rows selected.
SQL>
These are dates with times - you'd apply TO_CHAR with desired format mask to display it as you want, e.g.
select to_char(time_from + ((column_value - 1) * 60) / (24 * 60), 'hh:mi am') time
which results in
TIME
--------
07:00 AM
08:00 AM
09:00 AM
10:00 AM
11:00 AM
12:00 PM
01:00 PM
04:00 PM
05:00 PM
9 rows selected.
If you want to use "number of minutes" as parameter, then modify lines #9 and #12:
SQL> with test (time_from, time_to) as
2 (select to_date('09.08.2020 07:00', 'dd.mm.yyyy hh24:mi'),
3 to_date('09.08.2020 14:00', 'dd.mm.yyyy hh24:mi')
4 from dual union all
5 select to_date('09.08.2020 16:00', 'dd.mm.yyyy hh24:mi'),
6 to_date('09.08.2020 18:00', 'dd.mm.yyyy hh24:mi')
7 from dual
8 )
9 select to_char(time_from + ((column_value - 1) * &&par_minutes) / (24 * 60), 'hh:mi am') time
10 from test cross join
11 table(cast(multiset(select level from dual
12 connect by level <= (time_to - time_from) * 24 * (60 / &&par_minutes)
13 ) as sys.odcinumberlist));
Enter value for par_minutes: 20
old 9: select to_char(time_from + ((column_value - 1) * &&par_minutes) / (24 * 60), 'hh:mi am') time
new 9: select to_char(time_from + ((column_value - 1) * 20) / (24 * 60), 'hh:mi am') time
old 12: connect by level <= (time_to - time_from) * 24 * (60 / &&par_minutes)
new 12: connect by level <= (time_to - time_from) * 24 * (60 / 20)
TIME
--------
07:00 AM
07:20 AM
07:40 AM
08:00 AM
08:20 AM
08:40 AM
09:00 AM
09:20 AM
09:40 AM
10:00 AM
10:20 AM
10:40 AM
11:00 AM
11:20 AM
11:40 AM
12:00 PM
12:20 PM
12:40 PM
01:00 PM
01:20 PM
01:40 PM
04:00 PM
04:20 PM
04:40 PM
05:00 PM
05:20 PM
05:40 PM
27 rows selected.
SQL>