Quote:
Originally Posted by Arsenal14
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.
|
You used a few "AS" functions in there...does that mean they're newly defined variables...as in I don't have to create a new column in any of these tables that you've joined do, this function is taking care of that as it sees which rooms are available on a given date?