When is he free?

I thought this will be simple. I ended up with a pretty big SQL. Is there a simpler way?

Here’s the problem. A scheduling application has a table where everyone’s tasks are recorded with starting time and ending time. Further, there is a limitation that you should start or end tasks either at the beginning of the hour or at the middle of the hour (makes our life a bit easier). The normal working hours are from 8:30am to 12:30pm and from 1:30pm to 6:00pm. Given an employee id, and a date range (doesn’t have time component), what are the free time slots the person has? We would also like to aggregate adjacent time slots to one record. ie., If a person is free from 9:00-9:30 and 9:30-10:30, then we need one record that says 9:00-10:30.

Sample data is below.

```     EMPNO STARTDT      ENDDT        TASK
---------- ------------ ------------ ------------------------------
7369 apr-27 10:00 apr-27 10:30 Prepare presentations
7369 apr-27 14:30 apr-27 15:00 Review presentations
7369 apr-28 14:00 apr-28 16:00 Animate presentations
7369 apr-28 17:00 apr-28 18:00 Upload presentations
```

My solution is below. It uses pivot tables, analytic functions and hierarchical queries. May be I need to go outdoors for a long time, but is there a simpler solution?

1. Let us set some variables.
``` variable start_date varchar2(20); variable end_date varchar2(20); variable max_days_in_range number; variable empno number; variable time_slot_duration number; begin     :start_date := '27-APR-2004';     :end_date := '28-APR-2004';     :max_days_in_range := 100;     :empno := 7369;     :time_slot_duration := 0.5;     -- 0.25 for 15 minutes, 0.5 for 30 minutes etc.     -- Not tested for anything but 0.5 end; / ```
2. Step 1: Get all half an hour slots from a given range.
``` select startdt, enddt from (     select s.mydate, trunc(s.mydate)+(p.pivot*:time_slot_duration/24.0) startdt, trunc(s.mydate)+(:time_slot_duration/24.0)+(p.pivot*:time_slot_duration/24) enddt     from (select rownum pivot from all_objects where rownum <= 24.0/:time_slot_duration) p,         -- get 48 half an hour records per day          (select trunc(to_date(:start_date, 'DD-MON-YYYY'))+p.pivot-1 mydate          -- get all days between the given range             from (select rownum pivot from all_objects where rownum<=:max_days_in_range) p             --restriction: date range cannot span 100+ days             where trunc(to_date(:start_date, 'DD-MON-YYYY'))+p.pivot-1 <= trunc(to_date(:end_date, 'DD-MON-YYYY'))          ) s     order by 1 ) where (startdt between trunc(mydate)+8.5/24 and trunc(mydate)+12.0/24) or     (startdt between trunc(mydate)+13.5/24 and trunc(mydate)+17.5/24) ; ```
```STARTDT      ENDDT
------------ ------------
apr-27 08:30 apr-27 09:00
apr-27 09:00 apr-27 09:30
apr-27 09:30 apr-27 10:00
apr-27 10:00 apr-27 10:30
apr-27 10:30 apr-27 11:00
apr-27 11:00 apr-27 11:30
apr-27 11:30 apr-27 12:00
apr-27 12:00 apr-27 12:30
apr-27 13:30 apr-27 14:00
apr-27 14:00 apr-27 14:30
apr-27 14:30 apr-27 15:00
apr-27 15:00 apr-27 15:30
apr-27 15:30 apr-27 16:00
apr-27 16:00 apr-27 16:30
apr-27 16:30 apr-27 17:00
... and so on ..
```
3. Step 2: Get all scheduled tasks, expanded by half an hour increments. ie, 1 record from 10:30-12:00 will be turned into 3 records like 10:30-11:00, 11:00-11:30, 11:30-12:00.
``` select p.startdt, p.enddt from schedules s, (     -- step 1: Get all half an  hour slots from a given range     select startdt, enddt     from (         select s.mydate, trunc(s.mydate)+(p.pivot*:time_slot_duration/24.0) startdt, trunc(s.mydate)+(:time_slot_duration/24.0)+(p.pivot*:time_slot_duration/24) enddt         from (select rownum pivot from all_objects where rownum <= 24.0/:time_slot_duration) p,             -- get slot_hour records per day              (select trunc(to_date(:start_date, 'DD-MON-YYYY'))+p.pivot-1 mydate              -- get all days between the given range                 from (select rownum pivot from all_objects where rownum<=:max_days_in_range) p                 --restriction: date range cannot span 100+ days                 where trunc(to_date(:start_date, 'DD-MON-YYYY'))+p.pivot-1 <= trunc(to_date(:end_date, 'DD-MON-YYYY'))              ) s         order by 1     ) where (startdt between trunc(mydate)+8.5/24 and trunc(mydate)+12.0/24) or             (startdt between trunc(mydate)+13.5/24 and trunc(mydate)+17.5/24) ) p where s.empno = :empno and s.startdt <= p.startdt and s.enddt   >= p.enddt ; ```
```
STARTDT      ENDDT
------------ ------------
apr-27 10:00 apr-27 10:30
apr-27 14:30 apr-27 15:00
apr-28 14:00 apr-28 14:30
apr-28 14:30 apr-28 15:00
apr-28 15:00 apr-28 15:30
apr-28 15:30 apr-28 16:00
apr-28 17:00 apr-28 17:30
apr-28 17:30 apr-28 18:00
```
4. I’ve clubbed the code from remaining steps into the last step. I omitted results of intermediate steps for brevity -- but they are very interesting. Subtracting the results of step 2 from those of step 1 will give all the free slots available.
5. Step 3: But we want to "squeeze" the adjacent slots. ie., 10:30-11:00, 11:00-11:30 and 11:30-12:00 should show up as one record, 10:30-12:00. That can be done by first making sure start date stays the same till you get to a "busy" time. One way to do that is by using CONNECT BY and keeping track of start date. Note: even using CONNECT BY, this becomes a little bit easier with Oracle 10g using the new CONNECT_BY_ROOT function.
6. Step 4: We use LAG() and the running LEVEL to keep track of beginning start date.
7. Step 5: Now we can group on each branched start date to find the maximum end date. However, many start dates will have same the same end date. For example, free slot beginning at 8:30am, 9:00am, 9:30am etc. can all have an end time of 12:30pm.
8. Step 6: So, another group by end date to find the minimum start date. That will give us the longest available window of free time.
9. Putting it all together:
``` -- step 6: find the earliest start date for each end date select min(startdt) startdt, enddt from (     -- step 5: grouping on each branched startdt to find the maximum end time     select startdt, max(enddt) enddt     from (         -- step 4: set the startdt for each branch of the tree         select lag(startdt, depth-1) over (order by 1) startdt, enddt         from (             -- step 3: make a tree linked by startdt<-enddt             select level depth, startdt, enddt             from (                     -- step 1: Get all half an  hour slots from a given range                     select startdt, enddt                     from (                         select s.mydate, trunc(s.mydate)+(p.pivot*:time_slot_duration/24.0) startdt, trunc(s.mydate)+(:time_slot_duration/24.0)+(p.pivot*:time_slot_duration/24) enddt                         from (select rownum pivot from all_objects where rownum <= 24.0/:time_slot_duration) p,                         -- get 48 half an hour records per day                              (select trunc(to_date(:start_date, 'DD-MON-YYYY'))+p.pivot-1 mydate                              -- get all days between the given range                                 from (select rownum pivot from all_objects where rownum<=:max_days_in_range) p                                 --restriction: date range cannot span 100+ days                                 where trunc(to_date(:start_date, 'DD-MON-YYYY'))+p.pivot-1 <= trunc(to_date(:end_date, 'DD-MON-YYYY'))                              ) s                         order by 1                     ) where (startdt between trunc(mydate)+8.5/24 and trunc(mydate)+12.0/24) or                             (startdt between trunc(mydate)+13.5/24 and trunc(mydate)+17.5/24)                 minus                     -- step 2: get all scheduled tasks, expanded by half an hour increments                     -- ie, 1 record from 10:30-12:00 will be turned into 3 records like 10:30-11:00, 11:00-11:30, 11:30-12:00                     select p.startdt, p.enddt                     from schedules s, (                         -- step 1: Get all half an  hour slots from a given range                         select startdt, enddt                         from (                             select s.mydate, trunc(s.mydate)+(p.pivot*:time_slot_duration/24.0) startdt, trunc(s.mydate)+(:time_slot_duration/24.0)+(p.pivot*:time_slot_duration/24) enddt                             from (select rownum pivot from all_objects where rownum <= 24.0/:time_slot_duration) p,                             -- get slot_hour records per day                                  (select trunc(to_date(:start_date, 'DD-MON-YYYY'))+p.pivot-1 mydate                                  -- get all days between the given range                                     from (select rownum pivot from all_objects where rownum<=:max_days_in_range) p                                     --restriction: date range cannot span 100+ days                                     where trunc(to_date(:start_date, 'DD-MON-YYYY'))+p.pivot-1 <= trunc(to_date(:end_date, 'DD-MON-YYYY'))                                  ) s                             order by 1                         ) where (startdt between trunc(mydate)+8.5/24 and trunc(mydate)+12.0/24) or                                 (startdt between trunc(mydate)+13.5/24 and trunc(mydate)+17.5/24)                     ) p                     where s.empno = :empno                     and s.startdt <= p.startdt                     and s.enddt   >= p.enddt             )             connect by prior enddt = startdt         )     ) group by startdt ) group by enddt; ```
```STARTDT      ENDDT
------------ ------------
apr-27 08:30 apr-27 10:00
apr-27 10:30 apr-27 12:30
apr-27 13:30 apr-27 14:30
apr-27 15:00 apr-27 18:00
apr-28 08:30 apr-28 12:30
apr-28 13:30 apr-28 14:00
apr-28 16:00 apr-28 17:00
```

The entire script is given below.

``` set termout off drop table schedules; create table schedules (     empno number(4) not null,     startdt date not null,     enddt date not null,     task varchar2(30) not null ); insert into schedules(empno, startdt, enddt, task) values (7369,     to_date('040427 10:00','yymmdd hh24:mi'),to_date('040427 10:30','yymmdd hh24:mi'),     'Prepare presentations');      insert into schedules(empno, startdt, enddt, task) values (7369,     to_date('040427 14:30','yymmdd hh24:mi'),to_date('040427 15:00','yymmdd hh24:mi'),     'Review presentations'); insert into schedules(empno, startdt, enddt, task) values (7369,     to_date('040428 14:00','yymmdd hh24:mi'),to_date('040428 16:00','yymmdd hh24:mi'),     'Animate presentations'); insert into schedules(empno, startdt, enddt, task) values (7369,     to_date('040428 17:00','yymmdd hh24:mi'),to_date('040428 18:00','yymmdd hh24:mi'),     'Upload presentations'); alter session set nls_date_format='mon-dd hh24:mi'; set termout on select * from schedules; /*     I want to find all free slots employee 7369 has from 25-apr-2004 to 30-apr-2004     Grrr....!     Can be extended to include holiday processing.     Assumptions:         - Company works from 8:30-12:30 and 1:30-6:00 every day.         - People can start a task and end a task only at 0th or 30th minute of any hour.         - You can search for free slots over a date range (see the range above) that spans less than 101 days */ variable start_date varchar2(20); variable end_date varchar2(20); variable max_days_in_range number; variable empno number; variable time_slot_duration number; begin     :start_date := '27-APR-2004';     :end_date := '28-APR-2004';     :max_days_in_range := 100;     :empno := 7369;     :time_slot_duration := 0.5;     -- 0.25 for 15 minutes, 0.5 for 30 minutes etc.     -- Not tested for anything but 0.5 end; / Prompt -- step 1: Get all half an  hour slots from a given range select startdt, enddt from (     select s.mydate, trunc(s.mydate)+(p.pivot*:time_slot_duration/24.0) startdt, trunc(s.mydate)+(:time_slot_duration/24.0)+(p.pivot*:time_slot_duration/24) enddt     from (select rownum pivot from all_objects where rownum <= 24.0/:time_slot_duration) p,         -- get 48 half an hour records per day          (select trunc(to_date(:start_date, 'DD-MON-YYYY'))+p.pivot-1 mydate          -- get all days between the given range             from (select rownum pivot from all_objects where rownum<=:max_days_in_range) p             --restriction: date range cannot span 100+ days             where trunc(to_date(:start_date, 'DD-MON-YYYY'))+p.pivot-1 <= trunc(to_date(:end_date, 'DD-MON-YYYY'))          ) s     order by 1 ) where (startdt between trunc(mydate)+8.5/24 and trunc(mydate)+12.0/24) or     (startdt between trunc(mydate)+13.5/24 and trunc(mydate)+17.5/24) ; Prompt -- step 2: get all scheduled tasks, expanded by half an hour increments Prompt -- ie, 1 record from 10:30-12:00 will be turned into 3 records like 10:30-11:00, 11:00-11:30, 11:30-12:00 select p.startdt, p.enddt from schedules s, (     -- step 1: Get all half an  hour slots from a given range     select startdt, enddt     from (         select s.mydate, trunc(s.mydate)+(p.pivot*:time_slot_duration/24.0) startdt, trunc(s.mydate)+(:time_slot_duration/24.0)+(p.pivot*:time_slot_duration/24) enddt         from (select rownum pivot from all_objects where rownum <= 24.0/:time_slot_duration) p,             -- get slot_hour records per day              (select trunc(to_date(:start_date, 'DD-MON-YYYY'))+p.pivot-1 mydate              -- get all days between the given range                 from (select rownum pivot from all_objects where rownum<=:max_days_in_range) p                 --restriction: date range cannot span 100+ days                 where trunc(to_date(:start_date, 'DD-MON-YYYY'))+p.pivot-1 <= trunc(to_date(:end_date, 'DD-MON-YYYY'))              ) s         order by 1     ) where (startdt between trunc(mydate)+8.5/24 and trunc(mydate)+12.0/24) or             (startdt between trunc(mydate)+13.5/24 and trunc(mydate)+17.5/24) ) p where s.empno = :empno and s.startdt <= p.startdt and s.enddt   >= p.enddt ; Prompt -- Subtracting the results of step 2 from those of step 1 will give all the free slots available. Prompt -- But we want to "squeeze" the adjacent slots. ie., 10:30-11:00, 11:00-11:30 and 11:30-12:00 should show Prompt -- up as one record, 10:30-12:00 Prompt -- step 3 and 4: That can be done by first making sure start date stays the same till you get to a "busy" time. Prompt -- step 5: Then group it by that start date and find the maximum end date. However, many start dates will Prompt --         have same the same end date. Prompt -- step 6: So, another group by end date to find the minimum start date. That will give us the longest Prompt --         available window of free time. Prompt /* ** Putting it all together ** */ -- step 6: find the earliest start date for each end date select min(startdt) startdt, enddt from (     -- step 5: grouping on each branched startdt to find the maximum end time     select startdt, max(enddt) enddt     from (         -- step 4: set the startdt for each branch of the tree         select lag(startdt, depth-1) over (order by 1) startdt, enddt         from (             -- step 3: make a tree linked by startdt<-enddt             select level depth, startdt, enddt             from (                     -- step 1: Get all half an  hour slots from a given range                     select startdt, enddt                     from (                         select s.mydate, trunc(s.mydate)+(p.pivot*:time_slot_duration/24.0) startdt, trunc(s.mydate)+(:time_slot_duration/24.0)+(p.pivot*:time_slot_duration/24) enddt                         from (select rownum pivot from all_objects where rownum <= 24.0/:time_slot_duration) p,                         -- get 48 half an hour records per day                              (select trunc(to_date(:start_date, 'DD-MON-YYYY'))+p.pivot-1 mydate                              -- get all days between the given range                                 from (select rownum pivot from all_objects where rownum<=:max_days_in_range) p                                 --restriction: date range cannot span 100+ days                                 where trunc(to_date(:start_date, 'DD-MON-YYYY'))+p.pivot-1 <= trunc(to_date(:end_date, 'DD-MON-YYYY'))                              ) s                         order by 1                     ) where (startdt between trunc(mydate)+8.5/24 and trunc(mydate)+12.0/24) or                             (startdt between trunc(mydate)+13.5/24 and trunc(mydate)+17.5/24)                 minus                     -- step 2: get all scheduled tasks, expanded by half an hour increments                     -- ie, 1 record from 10:30-12:00 will be turned into 3 records like 10:30-11:00, 11:00-11:30, 11:30-12:00                     select p.startdt, p.enddt                     from schedules s, (                         -- step 1: Get all half an  hour slots from a given range                         select startdt, enddt                         from (                             select s.mydate, trunc(s.mydate)+(p.pivot*:time_slot_duration/24.0) startdt, trunc(s.mydate)+(:time_slot_duration/24.0)+(p.pivot*:time_slot_duration/24) enddt                             from (select rownum pivot from all_objects where rownum <= 24.0/:time_slot_duration) p,                             -- get slot_hour records per day                                  (select trunc(to_date(:start_date, 'DD-MON-YYYY'))+p.pivot-1 mydate                                  -- get all days between the given range                                     from (select rownum pivot from all_objects where rownum<=:max_days_in_range) p                                     --restriction: date range cannot span 100+ days                                     where trunc(to_date(:start_date, 'DD-MON-YYYY'))+p.pivot-1 <= trunc(to_date(:end_date, 'DD-MON-YYYY'))                                  ) s                             order by 1                         ) where (startdt between trunc(mydate)+8.5/24 and trunc(mydate)+12.0/24) or                                 (startdt between trunc(mydate)+13.5/24 and trunc(mydate)+17.5/24)                     ) p                     where s.empno = :empno                     and s.startdt <= p.startdt                     and s.enddt   >= p.enddt             )             connect by prior enddt = startdt         )     ) group by startdt ) group by enddt; set doc off; drop table schedules; ```
1. Hi Sir,
I need a help in writing Oracle SQL to find out start and end date for a week if we have month, year and week number .
for example
month = 04
year = 2004
weeknumber = 14

the start and end date for this week should be 28-MAR-04 to 03-APR-04 or if week starts from monday to sunday it should be 29-MAR-04 to 04-APR-04 . Please help me with this query
Thanks a lot
Bharati

Posted by: Bharati on May 1, 2004 12:45 AM
2. That is easy enough with pivots and few truncs.

select week_start, week_start+6 week_end from
(
select
trunc(
-- get first of the month
to_date('2004 04 01', 'yyyy mm dd'),
-- get the week beginning for that month
'day')
-- now get next weeks' beginnings
+ p.pivot*7 week_start
from (
-- about 5 week beginnings should be enough for a month
select rownum-1 pivot from all_objects where rownum<6) p
)
-- now select only the week we need
where to_char(week_start,'iw') = '14'
/

WEEK_START WEEK_END
-------------------- --------------------
2004.04.04 sunday 2004.04.10 saturday

I think in your calendar, week calculation is a off by one though.

Posted by: Babu on May 4, 2004 04:41 PM
3. Thie abov eis a good use of pivots although adding some truncs. would help the parser load time considerably ;)

Jenthony
http://www.jenthony.com

Posted by: Jenthony on July 4, 2004 08:04 AM
//-->