T-SQL Query Help Pt. II

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?

EDIT:

The guest reservation table looks like:

http://img535.imageshack.us/i/guestreservation.jpg/

The reservation table looks like:

http://img857.imageshack.us/i/reservation.jpg/

The invoice table has NOTHING i开发者_如何转开发n it currently.

The price plan table looks like:

I can't post more than too links but it's the same as the above with the name "priceplan.jpg"

Something like this might get you going in the right direction

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  

You need to do an INNER JOIN on the four tables and do your calculation:

Like this (untested):

select 
  GuestReservationTable.g_name, ((ReservationTable.res_checkout_date-ReservationTable.res_checkin_date) * PricePlanTable.rate) + InvoiceTable.inv_amount
from
  GuestReservationTable
  inner join ReservationTable on GuestReservationTable.confirm_no = ReservationTable.confirm_no
  inner join InvoiceTable on InvoiceTable .confirm_no = ReservationTable.confirm_no
  inner join PricePlanTable on PricePlanTable.price_plan = ReservationTable.PricePlan

You can use DATEDIFF to calculate the number of days and then use a join to get the values from each of the tables. The invoice table might not have a related row for the guest so it needs to be an outer join. The outer join means that inv_amount could be null so you need to check for that with ISNULL.

SELECT gr.guest_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

ISNULL at msdn http://msdn.microsoft.com/en-us/library/ms184325.aspx DateDiff help at msdn http://msdn.microsoft.com/en-us/library/ms189794.aspx