Need help in SQL Query with Stored Procedure in SqlServer 2008

Question

1.50/5 (2 votes)

See more:

Hi Guys,
I am doing Hotel Management System in Vb.Net_2008(Windows Application Project),SQL-Server_2008.
I have to check Room available or not with inputs of (CheckIn_Date,CheckOut_Date,Room_type,Room_Size,Floor_no)

I am using Two tables here.

1. Table Name - RoomDetails
Columns - Room_Id,Room_No,Room_type,Room_Size,Room_Rent,Floor_No

2. Table Name - BookingDetails
Columns - BookingId,CustomerId,RoomId,CheckIn_Date,CheckOut_Date,Booking_Date

Now, Using these two tables, how to write storedprocedure to get Availabily Rooms.

Giving Input - CheckIn_Date,CheckOut_Date,Room_type,Room_Size,Floor_no
Need OutPut coloums - Room_Id,Room_No,Room_Type,Room_Size,Room_Rent,Floor_no

Please Help to Get Output

Advance Thanks
By
Shajatha.

Posted 16-Apr-12 18:38pm

shajatha

Add a Solution

Comments

Sergey Alexandrovich Kryukov 17-Apr-12 1:17am

Not a question. Why cannot you do it?
--SA

shajatha 18-Apr-12 0:24am

Hi , Thanks for Comment, I am Learner for this Platform. I tried more times. But i got wrong result. That's why i asked here. I need that Searching Conditions only. Plz see my below coding..

ALTER PROCEDURE [dbo].[Usp_RoomAvailability_Checking] @Checkin datetime, @CheckOut datetime, @room_type nvarchar(50), @room_size nvarchar(50), @floor nvarchar(50) AS BEGIN Select RD.room_id,CONVERT(int,RD.room_no) as RoomNo ,RD.room_type ,RD.room_size ,RD.room_price ,RD.floor from tbl_room_details RD WHERE RD.room_type=@room_type and RD.room_size=@room_size and RD.floor=@floor except select RD.room_id,RD.room_no ,RD.room_type,RD.room_size,RD.room_price,RD.floor from tbl_room_details RD inner join tbl_booking_details BD on BD.RoomId=RD.room_id where BD.CheckIn <=@Checkin and (BD.CheckOut between @Checkin and @CheckOut) and RD.room_type=@room_type and RD.room_size=@room_size and RD.floor=@floor
End

walterhevedeich 17-Apr-12 1:45am

I suggest you try it and then come back if you have issues.

shajatha 18-Apr-12 0:16am

Hi, Thanks For Comment. I Tried Below Code, But it showing wrong result.

ALTER PROCEDURE [dbo].[Usp_RoomAvailability_Checking]
@Checkin datetime,
@CheckOut datetime,
@room_type nvarchar(50),
@room_size nvarchar(50),
@floor nvarchar(50)

AS
BEGIN

Select
RD.room_id,CONVERT(int,RD.room_no) as RoomNo ,RD.room_type ,RD.room_size ,RD.room_price ,RD.floor from tbl_room_details RD
WHERE RD.room_type=@room_type and RD.room_size=@room_size and RD.floor=@floor

except

select RD.room_id,RD.room_no ,RD.room_type,RD.room_size,RD.room_price,RD.floor
from tbl_room_details RD
inner join tbl_booking_details BD on BD.RoomId=RD.room_id
where BD.CheckIn <=@Checkin and (BD.CheckOut between @Checkin and @CheckOut) and RD.room_type=@room_type and RD.room_size=@room_size and RD.floor=@floor
End

I tried various conditions in above where conditions. Plz help me.
Advance thanks
Shaja

bbirajdar 17-Apr-12 2:57am

Not a question. Its a homework

shajatha 18-Apr-12 0:28am

Hi, Very very Thanks Your Comment. May be u r a Genius in this. I am Learner in this concept. If u can help in this .
Advance Thanks
Shaja

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

pratikv20 · Answer 1 · 2012-04-16T21:05:00

Solution 1

SQL

create procedure proc_name
@Room_type int,
@Room_Size int,
@Floor_no int,
@CheckIn_Date datetime,
@CheckOut_Date datetime

AS

BEGIN
select RD.Room_Id,RD.Room_No,RD.Room_Type,RD.Room_Size,RD.Room_Rent,RD.Floor_no
from RoomDetails RD inner join BookingDetails BD on RD.Room_Id =BD.Room_Id  where 
BD.CheckIn_Date not between @CheckIn_Date and @CheckOut_Date and BD.CheckOut_Date not between @CheckIn_Date and @CheckOut_Date and
RD.Room_type=@Room_type and RD.Room_Size=@Room_Size and RD.Floor_no=@Floor_no
END

Posted 16-Apr-12 21:05pm

pratikv20

Comments

shajatha 18-Apr-12 1:24am

Hi, Thanks a lot Your Reply. I tried Ur Coding with my Stored-procedure. Ur conditions not working correctly. Plz see my coding with Merged coding. If i give input @CheckOut lessthan CheckOut, its not working.

ALTER PROCEDURE [dbo].[Usp_RoomAvailability_Checking]

@Checkin datetime,
@CheckOut datetime,
@room_type nvarchar(50),
@room_size nvarchar(50),
@floor nvarchar(50)

AS

BEGIN

Select
RD.room_id,CONVERT(int,RD.room_no) as RoomNo ,RD.room_type ,RD.room_size ,RD.room_price ,RD.floor from tbl_room_details RD
WHERE RD.room_type=@room_type and RD.room_size=@room_size and RD.floor=@floor

except

select RD.room_id,RD.room_no,RD.room_type,RD.room_size,RD.room_price,RD.floor
from tbl_room_details RD inner join tbl_booking_details BD on RD.room_id =BD.RoomId where
BD.CheckIn not between @Checkin and @CheckOut and BD.CheckOut between @Checkin and @CheckOut and
RD.room_type=@room_type and RD.room_size=@room_size and RD.floor=@floor

END