SQL challenge

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 | +--------------------------+------------------------------------------+

Richard Spitz (Richard.Spitz@ana.med.uni-muenchen.de) wrote: : 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); : 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. CREATE TABLE CalenderDays ( When DATE NOT NULL, . ); CREATE TEMP TABLE Numbers ( Num INTEGER ); INSERT INTO Numbers VALUES ( 0 ); INSERT INTO Numbers VALUES ( 1 ); INSERT INTO Numbers VALUES ( 2 ); INSERT INTO Numbers VALUES ( 3 ); INSERT INTO Numbers VALUES ( 4 ); INSERT INTO Numbers VALUES ( 5 ); INSERT INTO Numbers VALUES ( 6 ); INSERT INTO Numbers VALUES ( 7 ); INSERT INTO Numbers VALUES ( 8 ); INSERT INTO Numbers VALUES ( 9 ); -- -- Create a calender of 20,000 days after 01/01/1970 -- INSERT INTO CalenderDays SELECT 25568 + N1.Num * 10000 + N2.Num * 1000 + N3.Num * 100 + N4.Num * 10 + N5.Num FROM Numbers N1, Numbers N2, Numbers N3, Numbers N4, Numbers N5 WHERE N1.Num < 3; CREATE INDEX Cal_Ndx ON CalenderDays ( When ); -- -- This will give you a list of the days and positions where the -- position is not fully occupied (plus the actual occupancy of the -- position on that day). -- SELECT E.pos_id, C.when, SUM(E.share) FROM Contract E, CalenderDays C WHERE C.When BETWEEN E.c_begin AND E.c_end AND C.When BETWEEN :Start_Date AND :End_Date GROUP BY E.pos_id, C.When HAVING SUM(E.Share) <> 100; From here, it's a matter of a TEMP table and a couple of other aggregate queries with self joins . Now this is actually an interesting problem, because (how did you ever guess?) it illustrates why the IDS/UD product is kind of cool. Another poster said, "Why not use an Overlap function?" and in the query above, it would certainly be useful. i.e. SELECT E.pos_id, C.when, SUM(E.share) FROM Contract E, CalenderDays C WHERE C.When BETWEEN E.c_begin AND E.c_end AND C.When BETWEEN :Start_Date AND :End_Date AND Overlaps ( Period ( E.c_begin, E.c_end ) , Period ( :Start_Date, :End_Date )) GROUP BY E.pos_id, C.When HAVING SUM(E.Share) <> 100; Depending on the selectivities -- how many positions etc -- the extra predicate here -- the temporal overlaps -- could significantly speed up the query. Using the R-Tree access method you could create an index on the Period ( E.c_begin, E.c_end ) function. Also, you probably want to *really* return a set of Periods when the position isn't fully occupied. This is a user-defined aggregate. Hope this helps! KR Pb

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? > My advice: don't waste your time on pure SQL solution. It probably exists - with proper number of temp tables and SPs - but certainly will be more cumbersome & slow then programming solution. The task is not trivial even for program. Looping through the days, as you intend, involves a few excessive steps. I would suggest the following algorithm. Define array of k (day date, p_share smallint) elements where k is the number of days in the desired interval. Fill day in the array (day(1)=beg of int,..., day(k)=end). Read table contract ordered by pos_id. For each position: Clean p_share in array with 0. For each row: If c_begin>=day(1) or c_end<=day(k) p_share(i)=p_share(i)+share for each i where c_begin <= day(i) <=c_end After pos_id: Array has information you need. The adjacent elements from k to l with the same p_share will produce one output row: pos_id, day(k), day(l), p_share. (Hopefully, logic with two nested cycles is not a problem) This is, probably, the shortest and fastest approach: the single scan of contract table. If table is big, you can create and drop index on pos_id. Let me know if you have questions.