Help with Hotel Queries in Microsoft SQL

[Devils'Advocate]

First, you have a ; after pp.price_plan.

Second, that's one funky "GROUP BY". I've never seen any syntax like that before in my life.

[Jedi Ninja]

In addition to the semi colon issue you just mentioned, I don' think you need Price in your subquery's GROUP BY.

I generally prefer to break down the query into Views in situations like this, as joining to nested queries like that is ugly and difficult to read.

FYI, you can also do a nested query in the SELECT portion of the clause, e.g.:

SELECT
gr.g_name,

Lodging_Cost = (DATEDIFF(d, r.res_checkout_date, r.res_checkin_date) * pp.rate ) + ISNULL(i.inv_amount, 0) ,

Total_Dining =
( SELECT SUM(price)
FROM dining_order do
JOIN dining_menu dm ON do.item = dm.item
WHERE do.r_confirmation_no = gr.r_confirmation_no)

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;