Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2013
    Posts
    5

    Unanswered: Can anyone help me with SQL Query

    1.) List the revenue for each hotel received so far i.e. for DepartureDate < NOW(). The calculation must be done in the SQL statement. Determine the length of each reservation (i.e. number of days) using the DateDiff function and multiply this value by the room rate (not the discounted rate). Include the hotel number in the output.

    2.) List all the rooms in each hotel that have never been reserved in order by hotel number.

    3.) List the guests by name and the number of times each has reserved a room at one of our hotels. Arrange the list in order from most-frequent to least-frequent guest.

    Here is the link for Relationships tables:

    Report1

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    List the attempts you have made to do your homework, and then 8dentify what parts you are struggling with and then there is a chance someone may help you out.

    You have (marginally) more chance if you provide the tables with sample data that proves the queries are right. That means data that will meet the needs of your test cases and some data that doesn`t
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2013
    Posts
    5

    Here is what i Tried

    For question number 1

    1.) SELECT ROOM.HotelNo, DateDiff("d", [ArrivalDate], [DepartureDate]) * ROOM_TYPE.RoomRate As TotalRevenue
    FROM RESERVATION, ROOM_TYPE, ROOM
    WHERE ROOM.RoomType = ROOM_TYPE.RoomType
    AND RESERVATION.RoomNo = ROOM.RoomNo
    AND DepartureDate > Now()
    Group by ROOM.HotelNo

    I'm getting error "Your query does not include the specific expression 'DateDiff("d", [ArrivalDate], [DepartureDate]) * ROOM_TYPE.RoomRate' as part of an aggregate funtion."

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1) Every column (actual or computed) in the SELECT part of a query must be separated from its preceeding one by a comma (,), so:
    Code:
    SELECT ROOM.HotelNo, DateDiff("d", [ArrivalDate], [DepartureDate]), *, ROOM_TYPE.RoomRate As TotalRevenue
    2) Except for aggregation functions (MAX, MIN, COUNT, AVG, etc.), you must repeat every column present in the SELECT part in the GROUP BY clause. Moreover, you cannot use the * in a GROUP BY clause. You'll then have to explicitly enumerate all the columns you want to see in the query and repeat this enumeration in the GROUP BY clause so:
    Code:
    GROUP BY ROOM.HotelNo, DateDiff("d", [ArrivalDate], [DepartureDate]), <other columns...>, ROOM_TYPE.RoomRate
    3) Be aware that you have no JOIN clause in your query.
    Have a nice day!

  5. #5
    Join Date
    Nov 2013
    Posts
    5

    I'm sorry bro I'm not including all columns

    Hi
    I'm not including all the columns
    I only need output with HOTELNO and TotalRevenue for each hotel.

    the " * " in the select statement is multiplying total number of days that comes from DateDiff to RoomRate.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    OK, I missed that one. Then you need to repeat the computed column (the formula, not the alias) in the GROUP BY clause:
    Code:
    SELECT ROOM.HotelNo, DateDiff("d", ([ArrivalDate], [DepartureDate]) * ROOM_TYPE.RoomRate) As TotalRevenue
    FROM RESERVATION, ROOM_TYPE, ROOM
    WHERE ROOM.RoomType = ROOM_TYPE.RoomType
    AND RESERVATION.RoomNo = ROOM.RoomNo
    AND DepartureDate > Now()
    Group by ROOM.HotelNo, (DateDiff("d", [ArrivalDate], [DepartureDate]) * ROOM_TYPE.RoomRate)
    Have a nice day!

Posting Permissions

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