My problem is trying to calcuate whether a room is booked during a date
period.
I have a table with two columns (Start and End date).
I need some SQL code to calculate whether a room is booked during a date
range.
e.g.
The booking entry is:
Start
21/11/2005
End
25/11/2005
Any help on this would be appreciated.
The End Date indicates the last night of the stay.
Regards,
Steven
*** Sent via Developersdex http://www.developersdex.com *** 5 2834
This is a good one. Please poke some holes in what I came up with.
How about this though?? Let me know how you make out. The two dates I
am "plugging" would be the beginning and ending date of the period.
create table room (
RoomNumber varchar(5),
StartDate datetime,
EndDate datetime
)
insert into room values('123','2 005-10-04','2005-10-05')
insert into room values('123','2 005-10-06','2005-10-07')
insert into room values('123','2 005-10-09','2005-10-10')
select distinct roomnumber
from room r1
where startdate not between '2005-10-08' and '2005-10-09'
and enddate not between '2005-10-08' and '2005-10-09'
and not exists(select * from room r2
where r2.roomnumber = r1.roomnumber
and startdate between '2005-10-08' and
'2005-10-09'
and enddate between '2005-10-08' and '2005-10-09')
---
Mark Graveline
Take The Challenge http://www.sqlchallenge.com
---
This problem has been driving me crazy lol
I run some tests on your code last night and it seems to work.
I am gonna give it a bit more testing tomorrow and adapt the code to
work with multiple rooms, so I will keep you posted.
Thanks for this help :)
Regards,
Steven
*** Sent via Developersdex http://www.developersdex.com ***
Hi,
what if you add a new column
alter table room
add RoomAvailable char(1)
where value is either 'Y' = room available
or 'N' = room not available
Regards
ActiveCrypt
---------------------------------------------- http://www.activecrypt.com
Hi,
Still having a problem.
I need to use two tables, one for Rooms and one for the actual Booking.
How could I modify this code to work with the two tables AND return me
ALL the rooms which are available.
At the moment, I can only return any rooms which have been previously
booked.
*** Sent via Developersdex http://www.developersdex.com ***
CREATE TABLE Rooms
(room_nbr INTEGER NOT NULL PRIMARY KEY,
bed_size CHAR(1) NOT NULL
CHECK (bed_size IN ('K', 'Q', 'T', 'D')),
..);
CREATE TABLE Bookings
(room_nbr INTEGER NOT NULL
REFERENCES Rooms(room_nbr)
start_date DATETIME DEFAULT CURRENT_TIMESTA MP NOT NULL,
end_date DATETIME, -- null mean occupied,
..); return me ALL the rooms which are available. <<
SELECT room_nbr
FROM Rooms
WHERE room_nbr
NOT IN (SELECT rom_nbr
FROM Bookings
WHERE @my_date BETWEEN start_date
AND COALESCE (end_date, CURRENT_TIMESTA MP);
Now create a calendar table for other things and get a copy of DSQL FOR
SMARTIES which has a whole section on this knd of query This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dave Robinson |
last post by:
I was wondering if anyone could help me with a problem I'm having.
I've been using Dreamweaver to create a hotel booking system for a
friend of mine, using MySQL (version 4.0.21) and PHP 5. The bit I'm
struggling with is checking the Room Availability based on dates that
are typed into a textfield and then returning a list of the available
rooms on the next page.
The three tables involved in this function are:
CREATE TABLE `room` (
|
by: Joan MacEachern |
last post by:
Date: Thursday, October 2, 2003
Time: 10-11am PT/ 1-2pm ET
Some business applications define mission critical. You
just can't afford to have them go down. Ever.
Is 'always on' a realistic goal? Mark Bauhaus, VP, Java Web
Services, and Rich Sharples, Senior Product Manager for
Application Server, explore this possibility as they examine
examples of high availability and introduce Sun's approach
|
by: Joan MacEachern |
last post by:
This paper presents an availability analysis for multitier,
Java(tm) and Web-based application deployments running on
the Sun(tm) ONE Application Server 7, Enterprise Edition
software. The results show that this software is able to
support 99.999-percent application availability in common
Web deployment.
http://java.sun.com/docs/performance/AS7-EE-5-Nines-HA-Evidence.pdf?ssobm=ng
|
by: Dan Williams |
last post by:
Can anyone tell me how to go about creating my own custom ASP page that can
show several different peoples availability from their Outlook/Exchange 2000
calendars?
I don't want to have to ask users to setup permissions for their respective
calendars, i just want to be able to see if they're busy or not.
Many thanks
Dan Williams.
|
by: Andy |
last post by:
Hi folks
I teach. At school, four IT rooms are booked using a paper based outline
timetable. Completing it is easy but basic and impossible to ensure
completion of all fields (name, year group, subject and software) and
analysis of bookings is a nightmare.
I just fancied pottering with Access, which I am pretty familiar with, to
see if I could create a means of booking a room using a database. Locking a
booking would then be a...
| |
by: John Coen |
last post by:
Help, does anyone have a database I could use to record downtime on multiple
systems, that will also show me availability percentages on a day to day
basis worked against actual service hours. If that wasn't enough I need to
be able to split between planned and unplanned downtime. Help!
|
by: Jeremy |
last post by:
How can I prove uptime (or quantify downtime) on an ASP.NET 1.1 Web
application (running on an IIS6 Windows Server Standard server)?
What's going on here is that I'm considering the feasability of claiming or
at least establishing some percentage of availability for our production Web
site (99.9%, or 99.99%, or 99.999% "five nines). Of course I'll only
advertise the fact if I can establish it objectively (and it's at least 3
nines :) For...
|
by: ALIABBAS J PETIWALA |
last post by:
N -ROOM LIGHTS PROBLEM
========================
THERE IS A BIG SQURE ROOM OF SIDE N WHICH CONSISTS OF N X N SMALLER
SQUARE ROOMS(ARRANGED LIKE CHESS BOARD)
EACH ROOM HAS A LIGHT.
WHEN the light of a smaller room k is toggled then all the
neighboring room's lights get toggled (max 5 lights get toggled
including the kth room if center room gets clicked )
|
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: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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: 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: 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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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...
| |