Re: SQL challenge

The tricky part is to determine the date range overlap. A neat way is to develop a SP to calculate maximum/mininum date between two date and the OVERLAP of two date range will be between MAX(date1,DATE1) and MIN(date2,DATE2) assuming date ranges are (date1,date2) and (DATE1,DATE2). But the following SQL using UNION will definitely work, I'm using date span '01/01/1998'-'12/31/1998' as an example. select a.pos_id,description, b.c_begin,date('12/31/1998'),share from position a, contract b where a.pos_id = b.pos_id and share < 100 and '01/01/1998' <= b.c_begin and '12/31/1998'>=b.c_begin UNION select a.pos_id,description, date('01/01/1998'),c_end, share from position a, contract b where a.pos_id = b.pos_id and share < 100 and '01/01/1998' <= b.c_begin and '12/31/1998'>=b.c_begin UNION select a.pos_id,description, c_begin,c_end, share from position a, contract b where a.pos_id = b.pos_id and share < 100 and '01/01/1998' <= b.c_begin and '12/31/1998'>=b.c_end; HTH Dong >From: Richard Spitz <Richard.Spitz@ana.med.uni-muenchen.de> >Reply-To: richard.spitz@ana.med.uni-muenchen.de >To: informix-list@iiug.org >Subject: SQL challenge >Date: Wed, 31 Mar 1999 12:30:31 +0200 > >Hi SQL Gurus, > >it seems like I'm suffering from a mental block. I've been pondering >about this for a while now, but haven't come up with a solution. Maybe >the collective wisdom of this group will help me. > >I have two tables, simplified schema as follows: > >create table position > (pos_id char(10), > description char(60)); >create unique index ix_position on position(pos_id);> >create table contract > (emp_id integer, > pos_id char(10), > c_begin date, > c_end date, > share smallint)); >create unique index ix_contract on contract(emp_id, pos_id, c_begin);> >This should be pretty self-explanatory: I have a number of positions, filled >with employees that have a contract for a certain position. Since positions >can be shared, the share of each contract can be between 0 and 100% (0% for >employees on a sabbatical, e.g.). > >The task now is to find vacant positions within a given time span (from - to >date), "vacant" meaning that a position is filled for less than 100%. The >query should return pos_id, beginning and end of the vacancy and the share >of the vacancy. > >Can this be done in SQL alone, or do I have to loop through the days within >the time span and check each single day, using 4GL or ESQL/C? > >Regards, Richard >-- >+--------------------------+------------------------------------------+ >| Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | >| EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | >| Klinikum Grosshadern | FAX : +49-89-7095-6420 <-- NEW!!! | >| 81366 Munich, Germany | GSM : +49-172-8933578 | >+--------------------------+------------------------------------------+ Get Your Private, Free Email at http://www.hotmail.com