SitePoint Sponsor

User Tag List

Results 1 to 15 of 15

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Help With Hotel Queries in SQL Server

    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?

  2. #2
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I figured out the 1st question...

    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'

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    where did you get the CREATE and ALTER statements from? did someone teach you to do it that way? or are you using some kind of scripting tool? because man, is the information you posted ever confusing

    try it like this instead --
    Code:
    CREATE TABLE reservation
    ( confirmation_no    INTEGER NOT NULL PRIMARY KEY
    , credit_card_no     CHAR(16)
    , res_checkin_date   DATETIME
    , res_checkout_date  DATETIME
    , default_villa_type CHAR(1)
    , price_plan         CHAR(1)
    );
    
    CREATE TABLE guest_reservation
    ( confirm_no         INTEGER      NOT NULL
    , agent_id           INTEGER      NOT NULL
    , g_name             VARCHAR(30)  NOT NULL
    , g_phone            VARCHAR(10)  NOT NULL
    , PRIMARY KEY ( confirm_no  
                  , agent_id    
                  , g_name      
                  , g_phone )
    );
    i've left out the foreign key stuff because it was riddled with errors

    is this a homework assignment?

    if so, and you want help with it, you will have to actually show us the results of your queries, and ask specific questions about it if the results aren't what you expect
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm yes I was taught this way (this is my first class in SQL Server so I apologize for errors).

    With regards to the orignial question I asked I think a piece of information was missing that would help with finding empty villas between particular dates:

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

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by smallville69 View Post
    How could I use that to help with finding empty Villa_ID's (which would represent the empty rooms in this case)?
    the villa table won't help at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm what next step then do you think I would need/take to get the output I need of viewing empty villas by type available on a certain range of days (checkout date - checkin date)?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i would suggest you query the reservation table, trying to find any reservations which overlap with the range of dates that the guest has requested

    we've had about a dozen threads on this very topic, and here's a recent one -- Detecting Inter-lapping Date Ranges - SitePoint Forums
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll take a look at that for now, thanks.

  9. #9
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    24/12/2010 is an arithmetic expression -- 24 divided by 12, divided by 2010

    '24/12/2010' is a date string

    see the difference?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay I was able to figure that out (it was because of the timestamp that was involved with the day I didn't realize so yes like you said it wasn't registering it so I had to break it down with the quotes).

    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?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    begin by figuring out how to join those tables
    Code:
    SELECT ...
      FROM reservation
    INNER
      JOIN pricing
        ON pricing._____ = reservation._____
    LEFT OUTER
      JOIN invoice
        ON invoice._____ = reservation._____
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    those queries look okay, syntactically

    of course, whether those are indeed the correct columns to join on, i wouldn't know

    one thing i wanted to ask you, why is there a reservation table as well as a guest_reservation table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •