vsbabu.org

When is he free?

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