Re: A neutral challenge.

From: Lauri Pietarinen <lauri.pietarinen_at_atbusiness.com>
Date: Wed, 25 Jun 2003 20:25:45 +0300
Message-ID: <3EF9DB19.7030008_at_atbusiness.com>

Here is my entry:

1. create a calendar with timeslots of your choice.

This table is generated and preloaded (has to be done only once) and can have extra precalculated columns to express holidays, weekdays, workdays etc etc. Extra columns can be added when needed.

Presuming a 10-year calendar with
1-minute timeslots we get a table with 24*60*365*10 = 5256000 rows.

I have here kept to a 10 minute caldender.

create table calender
(cal_id number(10) primary key,

   cal_date varchar2(8) not null,
   cal_time varchar2(4) not null,
   cal_txt  varchar2(50) not null,
   cal_weekday varchar2(20) not null,
   cal_is_work_day varchar2(1) not null);
  

insert into calender values(1,'20030625','1201','25.6.2003 at 12.01','Monday','Y');
insert into calender values(2,'20030625','1202','25.6.2003 at 12.02','Monday','Y');
insert into calender values(3,'20030625','1203','25.6.2003 at 12.03','Monday','Y');
insert into calender values(4,'20030625','1204','25.6.2003 at 12.04','Monday','Y');
insert into calender values(5,'20030625','1205','25.6.2003 at 12.05','Monday','Y');
insert into calender values(6,'20030625','1206','25.6.2003 at 12.06','Monday','Y');
insert into calender values(7,'20030625','1207','25.6.2003 at 12.07','Monday','Y');
insert into calender values(8,'20030625','1208','25.6.2003 at 12.08','Monday','Y');
insert into calender values(9,'20030625','1209','25.6.2003 at 12.09','Monday','Y');
insert into calender values(10,'20030625','1210','25.6.2003 at 12.10','Monday','Y');

2) create person table

create table person
 ( person_id number(10) primary key,
   person_name varchar2(50) not null );   

insert into person values (1, 'John');
insert into person values (2, 'Andrea');

3) create calendar entry table

create table cal_entry
(ce_id number(10) primary key,

   person_id number(10) not null,
   cal_id_start number(10) not null,
   cal_id_end number(10) not null,
   CONSTRAINT ce_person
     FOREIGN KEY (person_id)
       REFERENCES person,
   CONSTRAINT ce_cal_start
     FOREIGN KEY (cal_id_start)
       REFERENCES calender,
   CONSTRAINT ce_cal_end
     FOREIGN KEY (cal_id_end)
       REFERENCES calender        
            );
  

4) insert entries for each reserved time interval

insert into  cal_entry values(1,1,1,2);
insert into  cal_entry values(2,1,9,10);
insert into  cal_entry values(3,2,5,5);
insert into  cal_entry values(4,2,8,9);

5) get free timeslots (restrict to certain dates/days/times etc...)

create global temporary table free_slots  ( cal_id number(10) primary key );

• query to get all free timeslots (not occupied by either person)

insert into free_slots (cal_id)
select cal_id
  from calendar c
  where cal_is_work_day = 'Y' and

             cal_date between '20030625' and '20030630' and    not exists
    ( select *

            from cal_entry ce
                where person_id in (1,2) and
                      c.cal_id between ce.cal_id_start and ce.cal_id_end );
                         

select * from free_slots will give the following result:

cal_id

---

3
4
6
7

6) Group free timeslots into periods
(this can naturally be done programmatically, maybe with a bit less code)

select fs1.cal_id as start_i,

       fs2.cal_id as end_i,
           'from ' || c1.cal_txt || ' to ' || c2.cal_txt
  from free_slots fs1,
       free_slots fs2,
       calendar c1,
       calendar c2

  where
    fs1.cal_id = c1.cal_id and
    fs2.cal_id = c2.cal_id and
    not exists

          ( select *
              from free_slots fs3
                  where fs3.cal_id = fs1.cal_id-1 )
    and fs2.cal_id =
          ( select min(fs4.cal_id)
              from free_slots fs4
                  where fs4.cal_id > fs1.cal_id and
                    not exists
                          ( select *
                              from free_slots fs5
                                  where fs5.cal_id = fs4.cal_id+1 ) )

Result of the query (i.e. periods when both John and Andrea are available) is here:

3 4 from 25.6.2003 at 12.03 to 25.6.2003 at 12.04 6 7 from 25.6.2003 at 12.06 to 25.6.2003 at 12.07

7) after selecting the appropriate period new rows are inserted into cal_entry, e.g.

insert into cal_entry values(5,1,3,4);
insert into cal_entry values(6,2,3,4);

This solution does not address performance considerations, e.g. if we have thousands of persons with lots of entries.

I am, however, convinced that all performance issues can be addressed, mostly by indexing and in some cases perhaps with a (small) amount of procedural code.

regards,
Lauri Pietarinen Received on Wed Jun 25 2003 - 19:25:45 CEST