Help with Hotel Queries in Microsoft SQL

[iggyntangs]

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've created all the relevant 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 typical processes a hotel would go through (e.g. reservations).

So my thought process was to create 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 (you know like see if a suite or other type of room was available)?

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?

For the 1st query I think I'd have to use 3 of my tables; the room table, reservation table and rental table, which have the following columns:

Room Table: Room_ID, Room Type
Reservation Table: Confirmation_No, Check_In_Date, Check_Out_Date, Room_Type
Rental Table: Confirmation_No, Check_In_Date, Check_Out_Date, Room_Type

Basically 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?

For the 2nd query I think I'd have to use 3 tables again; the room table, price table and reservation table having the following columns:

Room Table: Room_ID, Room_Type, Price_Plan
Price Table: Price_Plan, Rate
Reservation Table: Check_In_Date, Check_Out_Date, Room_Type, Confirmation_No

Basically 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 of how to do this 'cuz I'm a total n00b?

Looking for ideas for what sort of functions I could/should use to get this started.

[Jedi Ninja]

There are many ways to skin a cat. Sometimes the following knives can come in handy:

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

NOT EXISTS(...) http://msdn.microsoft.com/en-us/library/ms188336.aspx

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

[BananaPancakes]

What class is this and where are you taking it? I'm interested in learning this too!

[iggyntangs]

To be able to find out a list of guests checking out on a specific date, grouped by villa type and providing a total number for that day (i.e. a count).

For that query I think I'd have to use 2 relevant tables a guest reservation and reservation table:

1. create table guest_reservation(
confirm_no int,
agent_id int,
g_name varchar (30),
g_phone varchar (10));

alter table guest_reservation
alter column confirm_no int
not null;

alter table guest_reservation
alter column agent_id int
not null;

alter table guest_reservation
alter column g_name varchar(30)
not null;

alter table guest_reservation
alter column g_phone varchar(10)
not null;

alter table guest_reservation
add constraint pk_guest_reservation primary key (confirm_no, agent_id, g_name, g_phone);

alter table guest_reservation
add constraint fk1_guest_reservation foreign key (confirm_no) references reservation
ON DELETE CASCADE;

alter table guest_reservation
add constraint fk2_guest_reservation foreign key (agent_ID) references agent
ON DELETE CASCADE;

alter table guest_reservation
add constraint fk3_guest_reservation foreign key (g_name, g_phone) references guest
ON DELETE CASCADE;

2. create table reservation(
confirm_no int,
credit_card_no char (16),
res_checkin_date datetime,
res_checkout_date datetime,
default_villa_type char (1),
price_plan char (1));

alter table reservation
alter column confirmation_no int
not null;

alter table reservation
add constraint pk_reservation primary key (confirmation_no);

alter table reservation
add constraint fk1_reservation foreign key (default_villa_type) references price_plan;

alter table reservation
add constraint fk2_reservation foreign key (price_plan) references price_plan;

I thought using a query like this would help, but it didn't seem to:
SELECT g_name, villa_type, COUNT(*) FROM guest_reservation, reservation
WHERE guest_reservation.confirm_no = reservation.confirm_no AND res_checkout_date = ‘insert date for when you would want to check out here’ GROUP BY villa_type;

Ideas/help?

[iggyntangs]

I think I figured out the 1st query...

Another query I wanted help on was that if a guest wanted a certain type of room then if that type of room would be available on the dates they wanted to stay on.

I used JUST the Reservation table but I'm not sure if that quite would do what I want, here's what I currently had:

Select villa_type from reservation
where res_check_in_date not between '2011-10-08' and '2011-10-09'
and res_check_out_date not between '2011-10-08' and '2011-10-09'

[Arsenal14]

For how many rooms of a type are available on the current date, something like:

select a.room_type, a.total_rooms - b.reserved_rooms as available_rooms from
(
select room_type, count(room_id) as total_rooms from room group by room_type
) a left outer join
(
select room_type, count(confirm_no) as reserved_rooms from reservation group by room_type where GETDATE() between res_checkin_date and res_checkout_date
) b on a.room_type = b.room_type

For total cost by confirmation number, something like:
select confirm_no, datediff(day, res_checkin_date, res_checkout_date) * rate as cost
from reservation, price, room
where room.price_plan = price.price_plan
and reservation.room_type = room.room_type

I haven't tried any of this and haven't been using SQL Server lately, but it might at least point you in the right direction.

[iggyntangs]

Originally Posted by Arsenal14

For how many rooms of a type are available on the current date, something like:

select a.room_type, a.total_rooms - b.reserved_rooms as available_rooms from
(
select room_type, count(room_id) as total_rooms from room group by room_type
) a left outer join
(
select room_type, count(confirm_no) as reserved_rooms from reservation group by room_type where GETDATE() between res_checkin_date and res_checkout_date
) b on a.room_type = b.room_type

For total cost by confirmation number, something like:
select confirm_no, datediff(day, res_checkin_date, res_checkout_date) * rate as cost
from reservation, price, room
where room.price_plan = price.price_plan
and reservation.room_type = room.room_type

I haven't tried any of this and haven't been using SQL Server lately, but it might at least point you in the right direction.

You used a few "AS" functions in there...does that mean they're newly defined variables...as in I don't have to create a new column in any of these tables that you've joined do, this function is taking care of that as it sees which rooms are available on a given date?

[Arsenal14]

"AS" just names the column when you select an expression (or can be used to rename a column in the result set). So you could do "select a, b, a-b as c from foo" and the result set would contain 3 columns called a, b, c with c being the difference between a and b.

[iggyntangs]

With regards to that last query (about total price for the customer) the part that's really confusing me and making it hard for me to find a total price one confirm_no owes (i.e. the one guest that is linked to that confirm_no) is that I have to add up other things in addition to the base pay, there's also an invoice table (for other rentals during their stay) that I need to add into the total the guest pays just for the room.

So:

create table invoice(
inv_no int,
inv_date datetime,
inv_amount money,
confirm_no int);

alter table invoice
alter column inv_no int
not null;

alter table invoice
add constraint pk_invoice primary key (inv_no);

alter table invoice
add constraint fk2_invoice foreign key (confirm_no) references rental ON DELETE CASCADE;

I need to take the total calculate originally (in your query) and add the inv_amount to that (which is linked through the confirm_no), how would I do that?

Ideas?

[Arsenal14]

I'd add a subquery that returns the invoice total grouped by confirm_no and then join that to the rest of the query. So:

select reservation.confirm_no, datediff(day, res_checkin_date, res_checkout_date) * rate as basecost, invoicecost, basecost + invoicecost as totalcost
from reservation, price, room,
(select confirm_no, sum(money) as invoicecost from invoice group by confirm_no) inv
where room.price_plan = price.price_plan
and reservation.room_type = room.room_type
and inv.confirm_no = reservation.confirm_no

[iggyntangs]

Originally Posted by Arsenal14

I'd add a subquery that returns the invoice total grouped by confirm_no and then join that to the rest of the query. So:

select reservation.confirm_no, datediff(day, res_checkin_date, res_checkout_date) * rate as basecost, invoicecost, basecost + invoicecost as totalcost
from reservation, price, room,
(select confirm_no, sum(money) as invoicecost from invoice group by confirm_no) inv
where room.price_plan = price.price_plan
and reservation.room_type = room.room_type
and inv.confirm_no = reservation.confirm_no

Thanks a lot for your help Arsenal, I'm going to try to incorporate these queries into my database when I get in tonight, I'll let you know how they work out. The logic there though makes sense to me, let's hope I can run it through!

[iggyntangs]

Hmm I don't think I was able to get Arsenal's queries to work properly for me,

I'm having trouble getting my queries to return appropriate results for the following queries though:

1. For a given specific date list the guests that are scheduled to check out, grouped by the villa type; additionally also present their count.

The guest reservation table has the following columns with data: (confirm_no, agent_id, g_name, g_phone)

The reservation table has the following columns with data: (confirm_no, credit_card_no, res_checkin_date, res_checkout_date, default_villa_type, price_plan).


SELECT g_name, default_villa_type
FROM guest_reservation, reservation
WHERE
guest_reservation.confirm_no = reservation.confirm_no
AND res_checkout_date = 24/12/2010
order by default_villa_type, g_name;


SELECT COUNT(*) as total_checking_out
FROM reservation
WHERE res_checkout_date = 24/12/2010

Shouldn't that query work? When I run it, I get NO errors simply no results returned (and the count is 0), and looking at my table I know there should be 2 guest names returning as they checkout that day.

Help?

[Arsenal14]

I think you need quotes around the date: '24/12/2010'. Otherwise, there might be issues doing an equality check on date times. If that doesn't work then one of the following might:
DateDiff(dd, res_checkout_date, '24/12/2010') = 0
or
res_checkout_date BETWEEN '24/12/2010' AND '24/12/2010'

[iggyntangs]

Okay I was able to figure that out (it was because of the timestamp that was involved with the day I didn't realize therefore the quotes always gave me an error, so I broke it down by year, month and day and it seemed to register that change quite well).

The last query I need help with is for a specific reservation show the tentative cost the guest will have to pay.

Now this is a bit more complicated because there are two costs, one is the cost for the duration of their stay and the other is for another invoice they get billed to them from another invoice (which is for things such as dining during their stay).

The guest reservation table has the following columns with data: (confirm_no, agent_id, g_name, g_phone)

The reservation table has the following columns with data: (confirm_no, credit_card_no, res_checkin_date, res_checkout_date, default_villa_type, price_plan)

The invoice table has the following columns with data: (inv_no, inv_date, inv_amount, confirm_no).

The price plan table has the following columns with data: (price_plan, rate, default_villa_type, bed_type)

So I need to somehow list the guests name with their total amount due which will be the ((res_checkout_date-res_checkin_date) * rate) + inv_amount coming from the reservation table, price table and invoice table respectively (and the guest name from the guest reservation table which is linked through the confirm_no).

It seems complicated and I'm not even sure where to begin...I tried looking at your model Arsenal for it but I got confused by it and the logic involved I'm sorry I am quite new to this I apologize.

[Mad Mel]

If I understand your data correctly, I'd approach it like this:

It appears that confirm_no on the reservation table is the primary key that you can use to join in most of the other tables.

So you should be able to join guest reservation to reservation using the confirm_no. Pretty straight forward.

Similarly, you should be able to join invoice to reservation on that column. However, invoice will potentially have zero records, or many records. What you need to do is get the total invoice, and join that. To do so, create a subquery (perhaps call it total_invoice) which is invoice grouped by confirm_no, and it sums inv_amount. Write the sql outside your main query and test it; it should return a row for each confirm_no with an amount. Add total_invoice to your view, and join on confirm_no. You will need to use an outer join, so that if there are no invoices associated with the reservation, the row will still show up in your view (it will have nulls for the invoice fields).

Then join price plan in onto the reservation table. I'm not sure of your data, you may just need to use price_plan, or both price_plan and default_villa_type.

Take a look at the output of your view after each join, and check that it has the columns and data that you need. At the end, add a column with your calculation.

[iggyntangs]

Mad Mel

I tried both

select
g.g_name,
datediff(d, r.res_checkin_date, r.res_checkout_date)*p.rate+i.inv_amount
from reservation as r
inner join price_plan as p
on r.price_plan = p.price_plan
inner join invoice as i
on r.confirm_no = i.confirm_no
inner join guest_reservation as g
on r.confirm_no = g.confirm_no

and

SELECT guest_reservation.g_name, (DATEDIFF(d, reservation.res_checkout_date, reservation.res_checkin_date) * price_plan.rate ) + invoice.inv_amount
FROM guest_reservation JOIN invoice ON guest_reservation.confirm_no = invoice.confirm_no
JOIN reservation ON guest_reservation.confirm_no = reservation.confirm_no
JOIN price_plan ON reservation.price_plan = price_plan.price_plan

Both of those queries gave me the same result:

I had NO errors however NO results returned only a g_name column and a "no column name" column and no results in them?

[iggyntangs]

So I figured out one query with the help here however I want to add one more thing into it now, so from before:

The guest reservation table has the following columns with data:

(confirm_no, agent_id, g_name, g_phone)

The reservation table has the following columns with data:

(confirm_no, credit_card_no, res_checkin_date, res_checkout_date, default_villa_type, price_plan)

The invoice table has the following columns with data:

(inv_no, inv_date, inv_amount, confirm_no).

The price plan table has the following columns with data:

(price_plan, rate, default_villa_type, bed_type)

So I need to somehow list the guests name with their total amount due which will be the ((res_checkout_date-res_checkin_date) * rate) + inv_amount coming from the reservation table, price table and invoice table respectively (and the guest name from the guest reservation table which is linked through the confirm_no).

To this I need to add what a guest may have ordered (food wise into their total)

So we have a dining_order table with the following columns with data:

(r_confirmation_no, item)

We have a dining_menu table with the following columns with data:

(item, price, description)

So with this query:

SELECT gr.g_name, (DATEDIFF(d, r.res_checkout_date, r.res_checkin_date) * pp.rate ) + ISNULL(i.inv_amount, 0) FROM guest_reservation gr LEFT OUTER JOIN invoice i ON gr.confirm_no = i.confirm_no JOIN reservation r ON gr.confirm_no = r.confirm_no JOIN price_plan pp ON r.price_plan = pp.price_plan;

I need to somehow add items that a guest has ordered from the dining_order table (which is linked with the r_confirm_no from the dining_order table equaling the confirm_no from the reservation table), the items price must be taken from the dining_menu table (where dining_order.item equals dining_menu.item) and added into the above query.

It can also be incorportated into this query which worked originally as well:

select g.g_name, datediff(d, r.res_checkin_date, r.res_checkout_date)*p.rate+coalesce(i.inv_amount, 0) as Amount from reservation as r
inner join priceplan as p on r.price_plan = p.price_plan inner join guest_reservation as g on r.confirm_no = g.confirm_no left outer join invoice as i on r.confirm_no = i.confirm_no

[Mad Mel]

Originally Posted by iggyntangs

Mad Mel

I tried both

select
g.g_name,
datediff(d, r.res_checkin_date, r.res_checkout_date)*p.rate+i.inv_amount
from reservation as r
inner join price_plan as p
on r.price_plan = p.price_plan
inner join invoice as i
on r.confirm_no = i.confirm_no
inner join guest_reservation as g
on r.confirm_no = g.confirm_no

and

SELECT guest_reservation.g_name, (DATEDIFF(d, reservation.res_checkout_date, reservation.res_checkin_date) * price_plan.rate ) + invoice.inv_amount
FROM guest_reservation JOIN invoice ON guest_reservation.confirm_no = invoice.confirm_no
JOIN reservation ON guest_reservation.confirm_no = reservation.confirm_no
JOIN price_plan ON reservation.price_plan = price_plan.price_plan

Both of those queries gave me the same result:

I had NO errors however NO results returned only a g_name column and a "no column name" column and no results in them?

For the 'no column name' issue, that's easy, you just need to assign your calc a column name:
SELECT guest_reservation.g_name, (DATEDIFF(d, reservation.res_checkout_date, reservation.res_checkin_date) * price_plan.rate ) + invoice.inv_amount AS Total_Amount

With respect to the missing data, the first thing to check is that all the tables you are using have data in the columns that you are joining on. Since you are using an inner join, if one of the tables doesn't have that confirmation number, that confirmation number won't be shown in the output.

[Mad Mel]

Originally Posted by iggyntangs

To this I need to add what a guest may have ordered (food wise into their total)

So we have a dining_order table with the following columns with data:

(r_confirmation_no, item)

We have a dining_menu table with the following columns with data:

(item, price, description)

If I interpret the data model correctly, you have many food items to sum up, and add to their total bill. To do that, join dining order and dining menu together so you have prices with each item they've ordered. Then group by r_confirmation_no and sum price. This will return a single row per r_confirmation_number with a total dining price. Take all of that and use it as a subquery joined to your main view, and add the dining price to the total.

Using subqueries is the least cluttered way to organise your database. Another method which is easier to follow when learning (but creates clutter) is to create views that you then join to the main view you are building. So you could create a total_dining_price view using the GROUP BY described above. Then add that view to your main view with a join, just like you would with any table.

[iggyntangs]

Originally Posted by Mad Mel

If I interpret the data model correctly, you have many food items to sum up, and add to their total bill. To do that, join dining order and dining menu together so you have prices with each item they've ordered. Then group by r_confirmation_no and sum price. This will return a single row per r_confirmation_number with a total dining price. Take all of that and use it as a subquery joined to your main view, and add the dining price to the total.

Using subqueries is the least cluttered way to organise your database. Another method which is easier to follow when learning (but creates clutter) is to create views that you then join to the main view you are building. So you could create a total_dining_price view using the GROUP BY described above. Then add that view to your main view with a join, just like you would with any table.

I thought the following query would work:

SELECT gr.g_name, (DATEDIFF(d, r.res_checkout_date, r.res_checkin_date) * pp.rate ) + ISNULL(i.inv_amount, 0) + ISNULL(d.total_dining, 0)
FROM guest_reservation gr
LEFT OUTER JOIN invoice i ON gr.confirm_no = i.confirm_no
JOIN reservation r ON gr.confirm_no = r.confirmation_no
JOIN price_plan pp ON r.price_plan = pp.price_plan;
LEFT OUTER JOIN (SELECT r_confirmation_no, SUM(price) as total_dining
FROM dining_order do JOIN dining_menu dm ON do.item = dm.item
GROUP BY r_confirmation_no, price) as d ON d.r_confirmation_no = gr.confirm_no

However I get the following error messages:

"Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'LEFT'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'as'."

Any ideas?