For how many rooms of a type are available on the current date, something like:
select a.room_type, a.total_rooms - b.reserved_rooms as available_rooms from
(
select room_type, count(room_id) as total_rooms from room group by room_type
) a left outer join
(
select room_type, count(confirm_no) as reserved_rooms from reservation group by room_type where GETDATE() between res_checkin_date and res_checkout_date
) b on a.room_type = b.room_type
For total cost by confirmation number, something like:
select confirm_no, datediff(day, res_checkin_date, res_checkout_date) * rate as cost
from reservation, price, room
where room.price_plan = price.price_plan
and reservation.room_type = room.room_type
I haven't tried any of this and haven't been using SQL Server lately, but it might at least point you in the right direction.
|