Hi,
I have a table with availability of rooms which has the
htlrm_id: hotel room id
avl_date: date this room is available
avl_qty: quantity of this room available
avl_rate: rate at which these rooms can be booked
an example of the data in the table is:
htlrm_id avl_date avl_qty avl_rate
2 20031005 14 140
2 20031006 3 20
2 20031007 4 40
5 20031005 1 100
5 20031006 1 100
5 20031007 0 120
My customer wishes to see whats available from 20031005 to 20031007
inclusive -- they just want to book one room.
Hotel room 5 is only available for 2 of those days so I only want to
return the rows for htl_id 2.
Using the statement:
SELECT
`tbl_availabili ty_temp`.`htlrm _id`,
`tbl_availabili ty_temp`.`avl_d ate`,
`tbl_availabili ty_temp`.`avl_q ty`,
`tbl_availabili ty_temp`.`avl_r ate`
FROM
`tbl_availabili ty_temp`
WHERE
((`tbl_availabi lity_temp`.`avl _date` BETWEEN '2003-10-05' AND
'2003-10-07') AND
(`tbl_availabil ity_temp`.`avl_ qty` > 0))
is bringing back:
htlrm_id avl_date avl_qty avl_rate
2 20031005 14 140
2 20031006 3 20
2 20031007 4 40
5 20031005 1 100
5 20031006 1 100
where I want to return:
htlrm_id avl_date avl_qty avl_rate
2 20031005 14 140
2 20031006 3 20
2 20031007 4 40
I'm working off mysql 3.23.53 --- so can't use nested selects like I
normally would.....
Any suggestions? I'd ideally like to keep it all on one statement...if
possible that is.
I've included the sql to create the tables and data:
CREATE TABLE `tbl_availabili ty_temp` (`htlrm_id` smallint(3) unsigned
NOT NULL default '0',
`avl_date` timestamp(8) NOT NULL,
`avl_qty` tinyint(3) unsigned NOT NULL default '0',
`avl_rate` decimal(4,2) NOT NULL default '0.00',
PRIMARY KEY (`htlrm_id`,`av l_date`),
KEY `htlrm_id` (`htlrm_id`,`av l_date`)
) TYPE=MyISAM;
INSERT INTO `tbl_availabili ty_temp` (`htlrm_id`, `avl_date`,
`avl_qty`, `avl_rate`) VALUES
(2,'2003-10-05',14,140),
(2,'2003-10-06',3,20),
(2,'2003-10-07',4,40),
(5,'2003-10-05',1,100),
(5,'2003-10-06',1,100),
(5,'2003-10-07',0,120);
I'm working with php 4.3.2 and mysql 3.23.53. 0 1680 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Mark |
last post by:
Hi - I have a numebr of rooms, which I am making available.
table: single - has single_id and single_name
rental_single has rental_id, single_id, check_in and check_out
They are linked by single_id.
I want to query so that when given two dates, it will return the
single_id that is free between two dates, which means I need to exclude
a singe_id if it has a linked entrey in the rental_single table, which
|
by: Catherine |
last post by:
I have a database where in my query I need to be able to locate
accounts that show a date greater than 14 days from the current date.
How would I structure this to allow for all accounts that over 14 days
old from today's date?
Any help you can give me would be greatly appreciated.
|
by: tony_aj_fox |
last post by:
Hello!
Hope y'all can help me. I've just started to use Access and just
putting together some simple asp pages for my band's website.
We've got a "latest news" table in our db and I want to create a
"what's on today" page which will select a article where today's date
falls between a field called startDate and the endDate field.
My SQL (from dreamweaver using vbscript) looks like:
|
by: dfetrow410 |
last post by:
Anyone have some code that will do this?
Dave
|
by: GregginOttawa |
last post by:
I've been Googling and reading for 5 days now trying to figure out how
to write a query that selects records with dates in the current week
(Sun to Sat). You'd think it would be remarkably simple (maybe it is),
but I can't find a simple answer.
If anyone could provide a relatively simple method, I would very much
appreciate it.
Thanks -
| |
by: vunderusaf |
last post by:
I have a listbox on a form that is selecting using named FinalQuery:
SELECT ., .,
.
FROM FinalQuery;
Now I have a text field with a date on this form and I'd like to use
that date as the selection criteria, comparing to the ...
how would I use a field with the WHERE clause?
|
by: Good Man |
last post by:
Hi there
I have a list of jobs scheduled in a MySQL table, with start dates and
end dates, like so:
SchedID | JobID | StartDate | EndDate |
----------------------------------------------
1 5 2006-05-08 2006-05-09
2 8 2006-05-10 2006-05-12
3 3 2006-05-01 2006-05-19
|
by: movieking81 |
last post by:
If someone could help me with this, that would be great. I need to select a number of records from an SQL table based on a date range, so I started with this select.
<html>
<code>
resultssql = "SELECT * FROM testtable where name = '" & request("name") & "' AND fromd >= '" & getdatefrom & "' AND fromd <= '" & getdateto & "'"
</code>
</html>
This select does find records, however they are not the correct ones. The records it finds only...
|
by: mankolele |
last post by:
Hi all.
I am having a tough time trying to find a way I can use to make restrictions to drop dows for dates, like the following dates are invalid and cannot be allowed to select them, April 31,June 31, September 31, November 31 and february 29 if not leap year.
How can I put a script for the dates.
Thank you nin advance.
|
by: lvjan777 |
last post by:
Access 2003 Windows XP Professional
I'm trying to open a report that displays all the records that are active during the user selected start and stop dates. I’m having trouble with the syntax since there are two input dates and two database dates to compare.
If they select 1 Apr 04 – 30 Apr 04 I want to see record 1.
If they select 1 May 04 – 30 May 04 I want to see record 1 and 2
Record 1
Start Date 12 Apr 04
Stop Date 30 Sep 07
|
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: 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: 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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: 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...
| |