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 1966
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
| |
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...
|
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...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
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...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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.
| |