query problem - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

query problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-12-10 : 07:45:57

writes "Hi SQL Team,
I've a query to be solved asap.
Thankx in advance that you people would help me out in this.

I have a room master, room booking master and a transaction table.
TB_ROOM_MST has
rm_id --> room id,
rm_name --> room name.

TB_BKNG_MST has
bkg_id --> Booking ID,
bkg_from --> Booking From Date and Time,
bkg_to --> Booking To Date and Time.

TB_BKNG_TXN has the booking transaction details.
bkg_txn_id --> Booking transaction ID,
bkng_id --> FK. refering the master table.
bkg_from --> Booking From Date.
bkg_to --> booking To Date.

Suppose let us assume there is a booking for room no. 1 from 01/12/2004 10:00 to 04/12/2004 14:00
and
01/12/2004 17:00 to 04/12/2004 19:00

Then the booking master will have the booking master will have one record.
1 | 01/12/2004 10:00 | 04/12/2004 14:00

And the Transaction table will have 4 entries.
1 | 1 | 01/12/2004 10:00 | 01/12/2004 14:00
2 | 1 | 02/12/2004 10:00 | 02/12/2004 14:00
3 | 1 | 03/12/2004 10:00 | 03/12/2004 14:00
4 | 1 | 04/12/2004 10:00 | 04/12/2004 14:00

I want to show the room availability.
If the user keys for searching:
the from date as 29/11/2004 10:00
and to date as 06/12/2004 17:00
and searches for the availability of the rooms,
and if the room master has 3 rooms in it,
R1 | Room 1
R2 | Room 2
R3 | Room 3

Then it should display the availability as:
(THERE IS NO BOOKING LIMIT FOR ROOMS.
ROOMS ARE AVAILABLE FOR BOOKING 24 HRS.
ie., from 00:00 to 23:59)

Room Name | Available From | Available To
Room 1 | 29/11/2004 00:00 | 29/11/2004 23:59
Room 1 | 30/11/2004 00:00 | 30/11/2004 23:59
Room 1 | 01/12/2004 00:00 | 01/12/2004 10:00
Room 1 | 01/12/2004 14:00 | 01/12/2004 17:00
Room 1 | 01/12/2004 19:00 | 01/12/2004 23:59
Room 1 | 02/12/2004 00:00 | 02/12/2004 23:59
Room 1 | 03/12/2004 00:00 | 03/12/2004 23:59
Room 1 | 04/12/2004 00:00 | 04/12/2004 23:59
Room 1 | 05/12/2004 00:00 | 05/12/2004 23:59
Room 1 | 06/12/2004 00:00 | 06/12/2004 23:59

Room 2 | 01/12/2004 00:00 | 01/12/2004 23:59
Room 2 | 02/12/2004 00:00 | 02/12/2004 23:59
Room 2 | 03/12/2004 00:00 | 03/12/2004 23:59
Room 2 | 04/12/2004 00:00 | 04/12/2004 23:59
Room 2 | 05/12/2004 00:00 | 05/12/2004 23:59
Room 2 | 06/12/2004 00:00 | 06/12/2004 23:59

Room 2 | 01/12/2004 00:00 | 01/12/2004 23:59
Room 2 | 02/12/2004 00:00 | 02/12/2004 23:59
Room 2 | 03/12/2004 00:00 | 03/12/2004 23:59
Room 2 | 04/12/2004 00:00 | 04/12/2004 23:59
Room 2 | 05/12/2004 00:00 | 05/12/2004 23:59
Room 2 | 06/12/2004 00:00 | 06/12/2004 23:59

This is what I want.
I am fed up with the query/proc writing this...
to solve this problem. I had already spent 2 days in it and
couldn't solve it.
I thought to use to insert a temporary table for availability
and to display the records from it.

PLEASE HELP ME IN THIS REGARD.
ANY SORT OF HELP FOR SOLVING THIS QUERY/PROCEDURE WILL BE GREATLY APPRICIATED.

I'm greatly thankful to the SQL Team for helping me.
Thanks a lot in advance."

KevinMunro
Starting Member

7 Posts

Posted - 2004-12-10 : 11:29:16

Why do you have a booking master table? Isn't this just storing the most recent transaction? If you dump this table I believe it will be simpler for you to code up.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-12 : 22:58:34

1. if the room is booked, do you still show this in your select for available rooms?
-- you may want to add another field if the room is booked or not?

2. in your booking master, why do you insert 4 records for one booking? you can remove the other 3 records, you can query datetime values using between such as: where datefield between @d1 and @d2

3. It seems you're employing header/details records, but from the fields you defined in Master and transaction you may only need one?

table could contain the BookID, RoomID, ClientID,DateFrom,DateTo

4. you can create a view for available rooms instead of sproc, if you're more comfortable with it.

HTH

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources