How to generate following report?
create table appointment_mas
(
app_id number,
app_start_date timestamp ,
app_end_date timestamp ,
doc_id number
);
insert into appointment_mas
values(1,to_date('19-Jul-17 09:00 AM','DD-Mon-YY HH12:MI AM'),to_date('19-Jul-17 01:00 PM','DD-Mon-YY HH12:MI AM'),11);
insert into appointment_mas
values(2,to_date('20-Jul-17 09:30 AM','DD-Mon-YY HH12:MI AM'),to_date('20-Jul-17 11:30 AM','DD-Mon-YY HH12:MI AM'),11);
insert into appointment_mas
values(3,to_date('20-Jul-17 09:00 AM','DD-Mon-YY HH12:MI AM'),to_date('20-Jul-17 02:00 PM','DD-Mon-YY HH12:MI AM'),22);
insert into appointment_mas
values(4,to_date('20-Jul-17 05:00 PM','DD-Mon-YY HH12:MI AM'),to_date('20-Jul-17 07:00 PM','DD-Mon-YY HH12:MI AM'),22);
insert into appointment_mas
values(5,to_date('20-Jul-17 04:00 AM','DD-Mon-YY HH12:MI AM'),to_date('20-Jul-17 05:00 PM','DD-Mon-YY HH12:MI AM'),11);
insert into appointment_mas
values(6,to_date('21-Jul-17 01:30 PM','DD-Mon-YY HH12:MI AM'),to_date('21-Jul-17 07:00 PM','DD-Mon-YY HH12:MI AM'),33);
commit;
select a.app_id,to_char(a.app_start_date,'dd-Mon-yy HH12:MI AM') as start_date ,
to_char(a.app_end_date,'dd-Mon-yy HH12:MI PM') as end_date , a.doc_id
from appointment_mas a ;
Output :-
----------------------------------------------------------------
App_id Start_date end_date doc_id
----------------------------------------------------------------
1 19-Jul-17 09:00 AM 19-Jul-17 01:00 PM 11
2 20-Jul-17 09:30 AM 20-Jul-17 11:30 AM 11
3 20-Jul-17 09:00 AM 20-Jul-17 02:00 PM 22
4 20-Jul-17 05:00 PM 20-Jul-17 07:00 PM 22
5 20-Jul-17 04:00 AM 20-Jul-17 05:00 PM 11
6 21-Jul-17 01:30 PM 21-Jul-17 07:00 PM 33
----------------------------------------------------------------But My appointment time-slace is 20 Minute per person like my expected output is below.
--------------------------------------------------------------
App_id Start_date end_date doc_id
--------------------------------------------------------------
1 19-Jul-17 09:00 AM 19-Jul-17 09:20 AM 11
1 19-Jul-17 09:21 AM 19-Jul-17 09:40 AM 11
1 19-Jul-17 09:41 AM 19-Jul-17 10:00 AM 11
1 19-Jul-17 10:01 AM 19-Jul-17 10:20 AM 11
1 19-Jul-17 10:21 AM 19-Jul-17 10:40 AM 11
1 19-Jul-17 10:41 AM 19-Jul-17 11:00 AM 11
1 19-Jul-17 11:01 AM 19-Jul-17 11:20 AM 11
1 19-Jul-17 11:21 AM 19-Jul-17 11:40 AM 11
1 19-Jul-17 11:41 AM 19-Jul-17 12:00 AM 11
1 19-Jul-17 12:01 PM 19-Jul-17 12:20 PM 11
1 19-Jul-17 12:21 PM 19-Jul-17 12:40 PM 11
1 19-Jul-17 12:41 PM 19-Jul-17 01:00 PM 11
and so on for remain record. ....
How it's possible to create above output using my appointment_mas table using select statement.
So you want to split each start/end range out into 20 minute chunks?
If so, generate a row for each 20 minute interval between the min start and max end. Then join to appointments where this value is between the start and end:
with rnge as (
select min(app_start_date) st, max(app_end_date) en
from appointment_mas
), rws as (
select st+(rownum/72) dt from dual
cross join rnge
connect by level <= extract(day from (en-st)) * 72
)
select app_id, dt st,
lead(dt, 1, app_end_date) over (partition by app_id order by dt) en
from rws
join appointment_mas
on dt >= app_start_date
and dt < app_end_date
order by 2;
APP_ID ST EN
---------- -------------------- --------------------
1 19-JUL-2017 09:20:00 19-JUL-2017 09:40:00
1 19-JUL-2017 09:40:00 19-JUL-2017 10:00:00
1 19-JUL-2017 10:00:00 19-JUL-2017 10:20:00
1 19-JUL-2017 10:20:00 19-JUL-2017 10:40:00
1 19-JUL-2017 10:40:00 19-JUL-2017 11:00:00
1 19-JUL-2017 11:00:00 19-JUL-2017 11:20:00
1 19-JUL-2017 11:20:00 19-JUL-2017 11:40:00
1 19-JUL-2017 11:40:00 19-JUL-2017 12:00:00
1 19-JUL-2017 12:00:00 19-JUL-2017 12:20:00
1 19-JUL-2017 12:20:00 19-JUL-2017 12:40:00
1 19-JUL-2017 12:40:00 19-JUL-2017 13:00:00
5 20-JUL-2017 04:00:00 20-JUL-2017 04:20:00
5 20-JUL-2017 04:20:00 20-JUL-2017 04:40:00
5 20-JUL-2017 04:40:00 20-JUL-2017 05:00:00
... etc. ...