• CELKO (9/27/2012)


    First, let's fix your DDL. There is no such thing a “type_id” in RDBMS; read ISO-11170 or any book on data modeling. Would you say “blood_type_id”? Of course not! A type and an id are totally different kinds of attribute properties. But why put it in the skeleton since we do not need it for the problem?

    The occupancy_end_date has to be NULL-able to show the guests who are still here (basic temporal idiom in SQL).

    --snip

    The system from which the data is taken deals with room types (single, double, family with view of wildebeest etc), not individual rooms. These are held in a master table which has an Id column.

    The table does not represent room occupancy: it represents room types at hotels which are on, or will be on, stop-sale. This is never an open interval.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.