SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 My query shows records which is not required
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vishaldb28
Starting Member

5 Posts

Posted - 11/20/2012 :  05:39:52  Show Profile  Reply with Quote
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
52309 Posts

Posted - 11/20/2012 :  06:02:22  Show Profile  Reply with Quote
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/

Go to Top of Page

vishaldb28
Starting Member

5 Posts

Posted - 11/20/2012 :  06:07:43  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 11/20/2012 :  06:48:33  Show Profile  Reply with Quote

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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/20/2012 :  06:58:31  Show Profile  Reply with Quote
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/

Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 11/20/2012 :  07:00:19  Show Profile  Visit webfred's Homepage  Reply with Quote
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
Go to Top of Page

vishaldb28
Starting Member

5 Posts

Posted - 11/20/2012 :  08:40:30  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 11/21/2012 :  05:37:44  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/25/2012 :  09:33:29  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000