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.
| Author |
Topic |
|
smallville69
Starting Member
10 Posts |
 Posted - 2011-04-06 : 17:58:05
|
| I posted this in another forum but now I think it may be better suited for this one. I have a couple of questions with a database I created for a class, we were supposed to create a sort of mock system for hotel reservations and processes that a made up hotel could go through, complete with multiple tables.I THINK I've created all the relavant tables and put in some mock data into it so that I can test it out and so far so good, however one of the guidelines was to create multiple queries of our choosing that we think would be relevant to the process.So my though process was that a query for: 1. If a guest wanted to reserve a certain type of room to see if that type of room is even available or if all are occupied?2. Maybe another one could be to take the time the guest wishes to stay and multiply it by the room rate to get a total for their entire stay?So for the first query I have a Guest table, a Room type table, then for each type of room I have separate tables for them (3), a guest reservation table, and a reservation table.I'm assuming I'd somehow have to JOIN these tables together selectively to output the information however I'm having trouble wrapping my head around how I could do that?For the 2nd query it seems somewhat simpler, I'm assuming I'd have to JOIN the reservation table and the price plan table and somehow get the two columns in one table (check in and check out date to subtract each other to get a duration) and then get that to multiply with the column of the other table which would be price of the specific room type to get a total.I understand the logistics behind the second query but am a bit confused as to how to reach that end point?Any suggestions or ideas? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-04-07 : 01:07:55
|
Give us sample tables, sample data and wanted result in relation to the sample data please.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
smallville69
Starting Member
10 Posts |
Posted - 2011-04-07 : 12:48:34
|
| For the 1st query I think I'd have to use 3 table; the room table, reservation table and rental table:Room Table: Room_ID, Room TypeReservation Table: Confirmation_No, Check_In_Date, Check_Out_Date, Room_TypeRental Table: Confirmation_No, Check_In_Date, Check_Out_Date, Room_TypeBasically I'd like to figure out a query to find out if a specific room type is available during certain days.So perhaps an output showing a list of all the rooms (by room_ID) from a certain room_type available between particular check_in and check_out days?Any ideas/suggestions? |
|
|
|
smallville69
Starting Member
10 Posts |
Posted - 2011-04-07 : 12:57:38
|
| For the 2nd query I think I'd have to use 3 tables again; the room table, price table and reservation table:Room Table: Room_ID, Room_Type, Price_PlanPrice Table: Price_Plan, RateReservation Table: Check_In_Date, Check_Out_Date, Room_Type, Confirmation_NoBasically I'd like to figure out a query to give a total cost of stay by Confirmation_No.So perhaps an output showing a list of confirmation numbers and then the check_out_date minus the check_in_date to give a duration of stay and then that column multiplied by the corresponding rate and a last column showing a total price?Any ideas/suggestions? |
|
|
|
|
|
|
|
|
Query Help