My query shows records which is not required

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

My query shows records which is not required

Author

Topic

vishaldb28
Starting Member

5 Posts

Posted - 2012-11-20 : 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

52326 Posts

Posted - 2012-11-20 : 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 - 2012-11-20 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-20 : 06:48:33

[code]
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;
[/code]

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-20 : 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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-20 : 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

vishaldb28
Starting Member

5 Posts

Posted - 2012-11-20 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-21 : 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

52326 Posts

Posted - 2012-11-25 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources