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 8 1975
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
--
Brilliant!
Thanks a lot David!! You saved my life!
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
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
--
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
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
--
Great! Thanks for saving my life again :-)
Thanks a lot!
Great! Thanks for saving my life again :-)
Thanks a lot! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 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.
|
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 0.
This is easy enough
Then I want to identify all rows where column C = 1 ,
|
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 in my data set to see if
it is null or not. If it is null I want to set my drop down list to a
certain value (where I'm using dropdown.SelectedValue = ). If it is not
null I want to set the drop down to the dataset value. Here is what the...
|
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 data. Depending upon the contents of a
particular field that cost calculation changes....
I retrieve the data in to a dataset and subsequently in to a datatable -
fine so far...
|
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 block
123.8
123.8
1234.567
12345
12345
1234.567
| |
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 exception when they are different.
Is there anyway i can modify the code below so that if the schemas are
different I can drop the offending column in the appropriate datatable?
Thanks,
Ron
|
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 Initial
3 3 15 Initial
|
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. I then need to iterate through the DataTable to get the values
out. This in itself is not a big issue.
Code something like...
|
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 where someone
suggested to me I was spending too much time writing error messages,
and that I was therefore missing the benefit of using a scripting
language. The idea, apparently, is that the PHP interpreter writes all
the error messages that are...
|
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, input box for units of service, description of the service and each row has its own dropdown list of unit fees that apply.
Each dynamically created row will return 3 values fee1_choice, fee1_unit and fee1_money.
Note The above informaton is...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |