Re: SQL challenge

Well, I can't find a fast SQL alone solution. But I think your contract table doesn't ensure the data correctness (may be I'm wrong). I.e. one employer can have two (or more) records for the same position with overlapping periods: emp_id pos_id c_begin c_end share 1 2 01.10.1999 01.20.1999 10 1 2 01.15.1999 01.20.1999 20 So, you will have to ensure the data correctness through application or a trigger. I have an idea (don't know how good is it) - to change the contract table as follow: create table contract (emp_id integer, pos_id char(10), work_day date, share smallint)); create unique index ix_contract on contract(emp_id, pos_id, work_day); And to insert a record for each day the employer is working on the project (sure it will make the application more complex). But in this case you can get your result very easy. Of course, you can make such a table while you are running your report, but in this case I think you will have to use 4GL/SPL/ESQL (at least I can't see yet any mode to make this in sql alone, may be someone there can). Best Regards, Octav On Wed, Mar 31, 1999 at 12:30:31PM +0200, Richard Spitz wrote: > 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 | > +--------------------------+------------------------------------------+ -- Octav Chiriac Phone: (373) 2 21 20 96 NetInfo S.R.L. Fax: (373) 2 21 36 59 Chisinau (373) 2 24 00 83 Moldova, Republic of mailto:com@netinfo-moldova.com