April 8, 2011 at 9:40 am
Hello I have a database in Microsoft SQL Server with tables relevant to a reservation system for a hotel/villa and need help creating a few queries to ontain relavant data:
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?
April 8, 2011 at 9:52 am
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;
you have a few problems that I see. first you must include g_name in the group by clause. since you are doing a count.
Second I am not sure if you put in ‘insert date for when you would want to check out here’ as part of your example or if you actually have that in you query however it would look for that exact string in the table.
you may also have to convert the date if the time is stored as parte of the date/time field. In other words '04/01/11' is actually '04/01/11 00:00:00.000' however if time is included in the reservation table then it will cause you problems since that would not be equalt to the exact time of 00:00:00.000.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 8, 2011 at 10:00 am
What is the relationship between guest_reservation and reservation? It looks like a one-to-one so it should probably be combined to one table.
smallville69 (4/8/2011)
...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).
Two separate questions, two queries:
SELECT g_name, villa_type
FROM guest_reservation, reservation
WHERE
guest_reservation.confirm_no = reservation.confirm_no
AND res_checkout_date = <your_date>
order by villa_type, g_name;
SELECT COUNT(*) as total_checking_out
FROM reservation
WHERE res_checkout_date = <your_date>
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 8, 2011 at 10:02 am
Todd that worked, thanks!
April 8, 2011 at 10:10 am
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'
April 8, 2011 at 10:40 am
We can't know that from the information we have. All we know is what types of villas are taken, but not which particular villas are taken. We can't know how many are available because we don't know how many there are! You need to break out into a separate table the individual villas, like:
create table villas(
villa_name varchar(20) not null primary key,
villa_type char(1) not null);
Now link these villas to the reservation instead of the type that is in there now. Then we can work on availability.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 8, 2011 at 11:08 am
I have created the following:
create table villa(
villa_ID INT,
phone_ext varchar(10),
villa_type char(1),
no_of_rooms TINYINT,
no_of_beds TINYINT,
default_price_plan CHAR(1),
);
alter table villa
alter column villa_id int
not null;
alter table villa
add constraint pk_villa primary key (villa_id);
How could I use that to help with finding empty Villa_ID's (which would represent the empty rooms in this case)?
April 8, 2011 at 2:37 pm
Is this a homework question?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 8, 2011 at 2:49 pm
Yes it is
April 9, 2011 at 2:27 pm
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?
April 9, 2011 at 3:18 pm
Okay I was able to figure that out (it was because of the timestamp that was involved with the day I didn't realize).
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?
April 9, 2011 at 4:37 pm
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?
April 9, 2011 at 5:38 pm
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply