| Author |
 Topic  |
|
|
vishaldb28
Starting Member
5 Posts |
 Posted - 11/20/2012 : 05:39:52
|
Hi All
I am trying to write a Hotel Room Booking System
I want to select those records only which are available between a range of dates.
Suppose 112 is book for 5 days say from 22/11/2012 to 27/11/2012, then this record of room no. 112 must not be diaplayed for these dates
22/11/2012
23/11/2012
24/11/2012
25/11/2012
26/11/2012
27/11/2012
Whether I select date range from 24/11/2012 to 26/11/2012 or from 21/11/2012 to 25/11/2012 or from 25/11/2012 to 27/11/2012. I my sql query is not performing what I want
SELECT roomdetail.room_no, room_type, room_bed, room_rate FROM roomdetail LEFT JOIN bookingtable ON roomdetail.room_no=bookingtable.room_no AND DATE(date_fro) >= 'strdtver1' AND DATE(date_to) <= 'strdtver2' WHERE bookingtable.room_no IS NULL;
Above query displays the room 112 which is already booked if I select dates range given in example.
Table structure -
roomdetail
Field Type Null Key Default Extra
room_no varchar(3) NO PRI NULL
room_type varchar(10) NO NULL
room_rate int(4) NO NULL
room_bed varchar(6) NO NULL
bookingtable
Field Type Null Key Default Extra
book_id int(3) NO PRI None
room_no varchar(3) YES NULL
date_fro datetime YES NULL
date_to datetime YES NULL
no_of_day int(3) YES NULL
I trying to do this by this way but not successfull
I need guidance and support pls help |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 11/20/2012 : 06:02:22
|
sounds like this to me
SELECT roomdetail.room_no, room_type, room_bed, room_rate FROM roomdetail LEFT JOIN bookingtable ON roomdetail.room_no=bookingtable.room_no AND DATE(date_fro) <= 'strdtver1' AND DATE(date_to) >= 'strdtver2' WHERE bookingtable.room_no IS NULL;
also where do you get those dates from?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
vishaldb28
Starting Member
5 Posts |
Posted - 11/20/2012 : 06:07:43
|
quote:
Originally posted by visakh16
sounds like this to me
SELECT roomdetail.room_no, room_type, room_bed, room_rate FROM roomdetail LEFT JOIN bookingtable ON roomdetail.room_no=bookingtable.room_no AND DATE(date_fro) <= 'strdtver1' AND DATE(date_to) >= 'strdtver2' WHERE bookingtable.room_no IS NULL;
also where do you get those dates from?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Hello visakh
I mention my both table structure in my post from where I am getting these data
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 11/20/2012 : 06:48:33
|
declare @roomdetail table(room_no varchar(3), room_type varchar(10), room_rate int , room_bed varchar(6))
declare @bookingtable Table (book_id int, room_no varchar(3), date_fro datetime, date_to datetime, no_of_day int)
INSERT into @roomdetail
values(112, 'ac', 14124, 'abc'), (113, 'ac', 1567, 'xyz')
SET DATEFORMAT DMY
insert into @bookingtable values(1, 112, '22/11/2012', '27/11/2012', 5),(2, 113, '12/10/2012', '12/11/2012', 30)
1) First solution
SELECT room_no, room_type, room_bed, room_rate
FROM @roomdetail
where room_no NOT IN ( SELECT room_no FROM @bookingtable WHERE cast(date_to as date) >= '25/11/2012' and cast(date_fro as date) <= '27/11/2012')
2) Second solution
SELECT r.room_no, room_type, room_bed, room_rate
FROM @roomdetail r
LEFT JOIN @bookingtable b
ON r.room_no=b.room_no
AND cast(date_to as date) >= '25/11/2012' AND cast(date_fro as DATE) <= '27/11/2012' WHERE b.room_no IS NULL;
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 11/20/2012 : 06:58:31
|
what are these strverdt1,strverdt2 etc?
Also DATE is not a standard function in t-sql
so my guess is you're using some other RDBMS
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8514 Posts |
Posted - 11/20/2012 : 07:00:19
|
You have already posted in DEVSHED and DBFORUMS and it is MySQL.
Here we are on MS SQL Server only so maybe the given solutions will not work for you...
Too old to Rock'n'Roll too young to die.
edit: typo |
Edited by - webfred on 11/20/2012 07:00:59 |
|
|
|
vishaldb28
Starting Member
5 Posts |
Posted - 11/20/2012 : 08:40:30
|
Hello Chandu
I knew I am doing something silly, and you shown me my mistake, Thank you.
Whether I am using MySQL, MS SQL, DB2 or any Database software SQL query Logic will be same for all of them, So I got it from MS SQL and implemented in MySQL and DB2
Anyways Thank you to all |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1430 Posts |
Posted - 11/21/2012 : 05:37:44
|
quote:
Originally posted by vishaldb28
Hello Chandu
I knew I am doing something silly, and you shown me my mistake, Thank you.
Whether I am using MySQL, MS SQL, DB2 or any Database software SQL query Logic will be same for all of them, So I got it from MS SQL and implemented in MySQL and DB2
Anyways Thank you to all
You are welcome..
--
Chandu |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 11/25/2012 : 09:33:29
|
quote:
Originally posted by vishaldb28
Hello Chandu
I knew I am doing something silly, and you shown me my mistake, Thank you.
Whether I am using MySQL, MS SQL, DB2 or any Database software SQL query Logic will be same for all of them, So I got it from MS SQL and implemented in MySQL and DB2
Anyways Thank you to all
not always
there are MS SQL specific syntaxes which may not work in MySQL,DB2 etc
so always safe to post on relevenat forums 
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
My query shows records which is not required
