DROP VIEW RoomsAvailable#
CREATE VIEW RoomsAvailable
(Hotel, Date, RoomType, RoomClass, HardRooms, SoftRooms, RoomsAvailable)
AS select
availheader.hotelcode as Hotel,
availheader.availdate,
availheader.roomtype as RoomType,
availheader.roomclass as RoomClass,
availheader.hardallocrooms as HardRooms,
availheader.softallocrooms as SoftRooms,
availheader.hardallocrooms - IFNULL(availusage.hardsuballoccount,0)  -
IFNULL(availusage.incountcheckin,0) - ifnull(availusage.incountgtd,0) -
ifnull(availusage.incountcnf,0) - ifnull(availusage.incountuncnf,0) -
ifnull(availusage.incountgrprmshdfc,0) - IFNULL(availusage.staycountcheckin,0) -
ifnull(availusage.staycountgtd,0) - ifnull(availusage.staycountcnf,0) -
ifnull(availusage.staycountuncnf,0) - ifnull(availusage.staycountgrprmshdfc,0)
as RoomsAvailable
from availheader LEFT OUTER JOIN availusage ON
availusage.availhdrptrsernum = availheader.serialnumber  and
availusage.availhdrptrdbnum = availheader.databasenumber
where availheader.ownfilenumber = 1170
#