From_Date AND To_Date in SQL join Query Problem

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

From_Date AND To_Date in SQL join Query Problem

Author

Topic

vishaldb28
Starting Member

5 Posts

Posted - 2012-04-03 : 14:44:26

Hi

I just want to display the list of unreserved rooms in a resort on an aspx page.

My database contain 2 simple tables
A. RoomDetail
RoomID RoomType RoomTariff
1 Deluxe 9000
2 Tree Top Cottage 16000
3 Mud Cottage 11000
4 Victorian Cottage 11000
5 Deluxe 9000
6 Tree Top Cottage 16000
7 Mud Cottage 11000
8 Victorian Cottage 11000
9 Deluxe 9000
10 Tree Top Cottage 16000
11 Mud Cottage 11000
12 Victorian Cottage 11000

B.BookingDetail
BookID RoomID BookedFrom BookedTo MailID
1 3 16-Mar-2012 19-Mar-2012 abc@gmail.com
2 2 17-Mar-2012 20-Mar-2012 xyz@female.com
3 1 17-Mar-2012 20-Mar-2012 pou@yahoo.com
4 2 21-Mar-2012 23-Mar-2012 tiger@kajal.com
5 3 20-Mar-2012 25-Mar-2012 khisiyani@sandi.com

This is my query -


String bookhistorysql = "SELECT RoomDetail.RoomID, RoomType,
RoomTariff FROM RoomDetail LEFT JOIN BookingDetail " + "ON
RoomDetail.RoomID=BookingDetail.RoomID AND BookedFrom >= '" + avdate
+ "' AND BookedTo <= '" + dpdate + "' WHERE BookingDetail.RoomID IS
NULL AND RoomType LIKE '" + rtype + "';";

Where avdate, dpdate, rtype are varibles which stores data from table.
Above query executes very fine (Not displaying Room No. 1) if I select 16-Mar-2012 and 19-Mar-2012
But if I select dates like 17-Mar-2012 and 19-Mar-2012 or 18-Mar-2012 and 20-Mar-2012
query displays the Room No. 1 which should not. Because Room No. 1 is book for 17-Mar-2012 and 20-Mar-2012.

I hope I mentioned my problem very clearly.
Can any body guide me in solving this problem.

Regards

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-03 : 14:54:35

Why are you using dynamic SQL?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

vishaldb28
Starting Member

5 Posts

Posted - 2012-04-03 : 14:57:52

Work Done, problem due to a small mistake
BookedFrom >= '" + avdate + "' AND BookedTo <= '" + dpdate
I just changed
BookedFrom >= '" + dpdate + "' AND BookedTo <= '" + avdate

X002548
Not Just a Number

15586 Posts

Posted - 2012-04-03 : 15:07:31

So you are not using stored procedures and you are making the SQL call from the front end?

It is less efficient doing that and could cause "miracles" to occur.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources