Checking obligated values in different rows

[Robert]

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

Robert

[David Portas]

Re: Checking obligated values in different rows

I 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_prij s) AS tarief_prijs
FROM Tarieven
WHERE einddatum >= @start_dt
AND startdatum <= @eind_dt
GROUP BY fk_object_id
HAVING MIN(tarief_prij s) = MAX(tarief_prij s)
AND MIN(startdatum) <= @start_dt
AND MAX(einddatum) >= @eind_dt

HAVING MIN(tarief_prij s) = MAX(tarief_prij s) 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
--

[Robert]

Re: Checking obligated values in different rows

Brilliant!

Thanks a lot David!! You saved my life!

[Robert]

Re: Checking obligated values in different rows

Hi David,

I hope I can use your brains again :-)

Your solution works perfectly except for one thing. If there is a gap in the
tariff information it will still return the object. Do you know of a
possibility to fix that?

Imagine the following information:

I'm trying to book a room from 12-oct-2004 to 21-oct-2004.

This is what the tariff table looks like
-----------------
FK_OBJECT_ID STARTDATUM EINDDATUM TARIEF_PRIJS
FK_OBJECT_ID STARTDATE ENDDATE TARIFF
1 10-oct-2004 16-oct-2004
100
1 19-oct-2004 22-oct-2004
125
------------------

There is no tariff information for the nights of 17 and 18 october, so the
object should not be returned as a valid object (no price calculation can be
made for those nights). Is there a way to do this?

Hope you can help me again!

Robert

[David Portas]

Re: Checking obligated values in different rows

First create a Calendar table to help with this one.

CREATE TABLE Calendar
(caldate DATETIME PRIMARY KEY)

Populate it:

INSERT INTO Calendar (caldate) VALUES ('20000101')

WHILE (SELECT MAX(caldate) FROM Calendar)<'2020 1231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATED IFF(D,'19991231 ',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar

Now you can do this:

SELECT @start_dt, @eind_dt,
fk_object_id, MIN(tarief_prij s) AS tarief_prijs
FROM Tarieven AS T
JOIN Calendar AS C
ON C.caldate
BETWEEN T.startdatum AND T.einddatum
AND C.caldate
BETWEEN @start_dt AND @eind_dt
GROUP BY fk_object_id
HAVING MIN(tarief_prij s) = MAX(tarief_prij s)
AND COUNT(DISTINCT C.caldate) > DATEDIFF(DAY,@s tart_dt,@eind_d t)

--
David Portas
SQL Server MVP
--

[Robert]

Re: Checking obligated values in different rows

Hi David,

I hope I can use your brains again :-)

Your solution works perfectly except for one thing. If there is a gap in the
tariff information it will still return the object. Do you know of a
possibility to fix that?

Imagine the following information:

I'm trying to book a room from 12-oct-2004 to 21-oct-2004.

This is what the tariff table looks like
-----------------
FK_OBJECT_ID STARTDATUM EINDDATUM TARIEF_PRIJS
FK_OBJECT_ID STARTDATE ENDDATE TARIFF
1 10-oct-2004 16-oct-2004
100
1 19-oct-2004 22-oct-2004
125
------------------

There is no tariff information for the nights of 17 and 18 october, so the
object should not be returned as a valid object (no price calculation can be
made for those nights). Is there a way to do this?

Hope you can help me again!

Robert

[David Portas]

Re: Checking obligated values in different rows

First create a Calendar table to help with this one.

CREATE TABLE Calendar
(caldate DATETIME PRIMARY KEY)

Populate it:

INSERT INTO Calendar (caldate) VALUES ('20000101')

WHILE (SELECT MAX(caldate) FROM Calendar)<'2020 1231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATED IFF(D,'19991231 ',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar

Now you can do this:

SELECT @start_dt, @eind_dt,
fk_object_id, MIN(tarief_prij s) AS tarief_prijs
FROM Tarieven AS T
JOIN Calendar AS C
ON C.caldate
BETWEEN T.startdatum AND T.einddatum
AND C.caldate
BETWEEN @start_dt AND @eind_dt
GROUP BY fk_object_id
HAVING MIN(tarief_prij s) = MAX(tarief_prij s)
AND COUNT(DISTINCT C.caldate) > DATEDIFF(DAY,@s tart_dt,@eind_d t)

--
David Portas
SQL Server MVP
--

[Robert]

Re: Checking obligated values in different rows

Great! Thanks for saving my life again :-)

Thanks a lot!

[Robert]

Re: Checking obligated values in different rows

Great! Thanks for saving my life again :-)

Thanks a lot!