473,320 Members | 2,109 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

Robert
Jul 20 '05 #1
8 1949
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_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
--
Jul 20 '05 #2
Brilliant!

Thanks a lot David!! You saved my life!
Jul 20 '05 #3
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
Jul 20 '05 #4
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)<'20201231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(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_prijs) 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_prijs) = MAX(tarief_prijs)
AND COUNT(DISTINCT C.caldate) > DATEDIFF(DAY,@start_dt,@eind_dt)

--
David Portas
SQL Server MVP
--
Jul 20 '05 #5
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
Jul 20 '05 #6
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)<'20201231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(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_prijs) 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_prijs) = MAX(tarief_prijs)
AND COUNT(DISTINCT C.caldate) > DATEDIFF(DAY,@start_dt,@eind_dt)

--
David Portas
SQL Server MVP
--
Jul 20 '05 #7
Great! Thanks for saving my life again :-)

Thanks a lot!
Jul 20 '05 #8
Great! Thanks for saving my life again :-)

Thanks a lot!
Jul 20 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Robert | last post by:
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...
3
by: jr | last post by:
A perplexing one this. I Am trying to design a query or series of queries which will firstly identify a condition. If column A value is less than column B value make column C value =1 , else...
2
by: Andy G | last post by:
How can I check this for null? dsPrsn.Tables(0).Rows(0)("WORK_STATE") I tried If IsDbNull(dsPrsn.Tables(0).Rows(0)("WORK_STATE")) Then it seems not too work. I am attempting to check this field...
2
by: Stuart | last post by:
Hi there I have a stored procedure on my SQL database that retrieves a wide range of values from about 5 different tables. My end point is to calculate the cost against each line of retrieved...
1
by: David | last post by:
I have rows of 8 numerical values in a text file that I have to parse. Each value must occupy 10 spaces and can be either a decimal or an integer. // these are valid - each fit in a 10 character...
2
by: RSH | last post by:
Hi, Iam struggling with an application where I am trying to transfer a datarow from one sql server to another instance of sql server. The schmeas may be slightly different and I am getting an...
0
by: Zamael | last post by:
Currently I have a dataset that populates as follows (example data): ID Cycle Drug_Day Name 3 1 1 Initial 3 1 15 Initial 3 2 1 Initial 3 2 15 Initial 3 3 1 ...
3
by: Martyn Fewtrell | last post by:
Hi there. I wonder if there was a "correct answer" for Vb.Net (I understand this is quite easy in C#). If im drawing data from a database using a TableAdapter the data ends up in a DataTable....
16
by: lawrence k | last post by:
I've made it habit to check all returns in my code, and usually, on most projects, I'll have an error function that reports error messages to some central location. I recently worked on a project...
2
by: assgar | last post by:
Hi Developemnt on win2003 server. Final server will be linux Apache,Mysql and PHP is being used. I use 2 scripts(form and process). The form displays multiple dynamic rows with chechboxs,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.