Re: SQL QUESTION

[ Question: given a table with start/end intervals, produce SQL to   display both present and absent intervals, with status ]

    This is a *great* question to use on students. I'll have to keep it in mind. The following assumes a) that periods don't overlap, and b) all date fields have the same clock time - only the calendar data is different.

    select begin_date, end_date, 'OCC' status     from table_x
    union
    select p.end_date + 1 "begin_date", f.begin_date - 1 "end_date",

           'FREE' status
    from table_x f, table_x p
    where not exists ( select 'x' from table_x

			where begin_date = p.end_date + 1 )
      and f.begin_date = ( select min( begin_date ) from table_x
			   where begin_date > p.end_date );

If periods overlap, change the 'not exists' clause to:

    where not exists ( select 'x' from table_x

			where begin_date = p.end_date + 1
			or ( begin_date < p.end_date
			     and end_date > p.end_date ) )

You will get some duplication of time intervals for the occupied periods, but the unoccupied periods will still be shown clearly. If the status info is what's really important, that should be good enough. If not, let me know and I'll cook up the query to avoid overlaps.

-- 
Mike Stein			The above represents the Absolute Truth.
POB 10420			Therefore it cannot possibly be the official
Arlington, VA  22210		position of my employer.