Checking obligated values in different rows

Hi there!

I have a problem I can't get rid of... I hope anyone can help me with it!

For a room-booking application I need to select available rooms for a given
period. First I do the availability check which is no problem, than I need
to find out if there is a tariff available for every night of the given
period. If not, I will not show the room as 'available' in the system
because I can't make a tariff calculation.

All tariffs are entered in a table which can hold different night-tariffs
for different periods. The table looks like this:

========
CREATE TABLE [dbo].[TARIEVEN] (
[TARIEF_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[FK_OBJECT_ID] [numeric](18, 0) NOT NULL ,
[STARTDATUM] [datetime] NULL ,
[EINDDATUM] [datetime] NULL ,
[TARIEF_PRIJS] [decimal](10, 2) NULL ,
) ON [PRIMARY]
GO

FK_OBJECT_ID is the Room ID
STARTDATUM is the startdate of a tariff period
EINDDATUM is the enddate of a tariff period
TARIEF_PRIJS is the tariff per night in the tariff period
========

What I do at the moment is:
---
SELECT fk_object_id FROM TARIEVEN
where ('15-oct-2004' BETWEEN STARTDATUM AND EINDDATUM)
AND ('16-oct-2004' BETWEEN STARTDATUM AND EINDDATUM)
AND ('17-oct-2004' BETWEEN STARTDATUM AND EINDDATUM)
---

This will work if all nights are in the same tariff period, but it will not
produce the right results if the tariffs are in different periods because no
single row contains a startdate and enddate in between which all dates are.
I can't use an OR statement because I absolutely need a tariff per night.

So, what my question is in short:
How can I for example select OBJECT 1 from the following table considering
that I want to return all rooms which have a tariff available for a period
from 15th of october to 17th of october?

------
FK_OBJECT_ID STARTDATUM EINDDATUM TARIEF_PRIJS
FK_OBJECT_ID STARTDATE ENDDATE TARIFF
1 10-oct-2004 16-oct-2004
100
1 17-oct-2004 18-oct-2004
125

In words: object 1 costs 100 per night in the period from 10 to 16 october
and 125 in the period from 17 to 18 october
------

I hope anyone can help me. Thanks a lot in advance!!

RobertI will assume that the Tarieven table won't contain overlapping dates, in
other words there can only be a single tariff per room per day.

DECLARE @.start_dt DATETIME, @.eind_dt DATETIME

/* The required date range: */
SET @.start_dt = '20041015'
SET @.eind_dt = '20041017'

SELECT @.start_dt, @.eind_dt,
fk_object_id, MIN(tarief_prijs) AS tarief_prijs
FROM Tarieven
WHERE einddatum >= @.start_dt
AND startdatum <= @.eind_dt
GROUP BY fk_object_id
HAVING MIN(tarief_prijs) = MAX(tarief_prijs)
AND MIN(startdatum) <= @.start_dt
AND MAX(einddatum) >= @.eind_dt

HAVING MIN(tarief_prijs) = MAX(tarief_prijs) ensures a single tariff.

The last two predicates exclude the case where the Tarieven table only
includes rows for part of the required period - in other words there was
only a single tariff for that room but it didn't cover the whole of the
required period.

--
David Portas
SQL Server MVP
--|||Brilliant!

Thanks a lot David!! You saved my life!