DROP View HotelAvailability#
CREATE VIEW HotelAvailability
(HotelCode,AvailDate,
RoomType, RoomClass,
HardRooms, SoftRooms,
RoomsAvailable,
RoomsReserved,
UnsoldAllocations,
RoomsArriving,
RoomsStayingOver,
GuaranteedRooms,
ConfirmedRooms,
UnconfirmedRooms,
GroupRooms,
RoomsCheckedIn,
AdultGuests,
ChildGuests,
InfantGuests,
UnnamedGroupRooms) AS
SELECT availheader.hotelcode, availheader.Availdate,
availheader.roomtype, availheader.roomclass,
availheader.hardallocrooms, availheader.softallocrooms,
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,
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 RoomsReserved,
ifnull(availusage.hardsuballoccount,0) as UnsoldAllocations,
ifnull(availusage.incountcheckin,0) + ifnull(availusage.incountgtd,0) +
ifnull(availusage.incountcnf,0) + ifnull(availusage.incountuncnf,0) +
ifnull(availusage.incountgrprmshdfc,0) as RoomsArriving,
ifnull(availusage.staycountcheckin,0) + ifnull(availusage.staycountgtd,0) +
ifnull(availusage.staycountcnf,0) + ifnull(availusage.staycountuncnf,0) +
ifnull(availusage.staycountgrprmshdfc,0) as RoomsStayingOver,
ifnull(availusage.incountgtd,0) + ifnull(availusage.staycountgtd,0) as GuaranteedRooms,
ifnull(availusage.incountcnf,0) + ifnull(availusage.staycountcnf,0) as ConfirmedRooms,
ifnull(availusage.incountuncnf,0) + ifnull(availusage.staycountuncnf,0) as UnconfirmedRooms,
ifnull(availusage.incountgrprmshdfc,0) + ifnull(availusage.staycountgrprmshdfc,0) as GroupRooms,
ifnull(availusage.incountcheckin,0) + ifnull(availusage.staycountcheckin,0) as CheckedInRooms,
ifnull(availusage.inadults,0) + ifnull(availusage.stayadults,0) as AdultGuests,
ifnull(availusage.inchildren,0) + ifnull(availusage.staychildren,0) as ChildGuests,
ifnull(availusage.ininfants,0) + ifnull(availusage.stayinfants,0) as InfantGuests,
ifnull(availusage.groupunsold,0) as UnnamedGroups
FROM availheader LEFT OUTER JOIN availusage ON
availusage.availhdrptrsernum = availheader.serialnumber  and
availusage.availhdrptrdbnum = availheader.databasenumber
WHERE availheader.ownfilenumber = 1170