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.:
Quote:
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;
|