Quote:
Originally Posted by Mad Mel
If I interpret the data model correctly, you have many food items to sum up, and add to their total bill. To do that, join dining order and dining menu together so you have prices with each item they've ordered. Then group by r_confirmation_no and sum price. This will return a single row per r_confirmation_number with a total dining price. Take all of that and use it as a subquery joined to your main view, and add the dining price to the total.
Using subqueries is the least cluttered way to organise your database. Another method which is easier to follow when learning (but creates clutter) is to create views that you then join to the main view you are building. So you could create a total_dining_price view using the GROUP BY described above. Then add that view to your main view with a join, just like you would with any table.
|
I thought the following query would work:
SELECT gr.g_name, (DATEDIFF(d, r.res_checkout_date, r.res_checkin_date) * pp.rate ) + ISNULL(i.inv_amount, 0) + ISNULL(d.total_dining, 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.confirmation_no
JOIN price_plan pp ON r.price_plan = pp.price_plan;
LEFT OUTER JOIN (SELECT r_confirmation_no, SUM(price) as total_dining
FROM dining_order do JOIN dining_menu dm ON do.item = dm.item
GROUP BY r_confirmation_no, price) as d ON d.r_confirmation_no = gr.confirm_no
However I get the following error messages:
"Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'LEFT'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'as'."
Any ideas?