IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_FreeBeds')))
THEN Drop view v_FreeBeds
END IF

GO

create view
  DBA.v_FreeBeds
  as select HotelName=(select H.HotelName from DBA.Hotels as H,DBA.Registers as R where lcase(R.RegisterName) = lcase('hotelid') and Hotel_ID = R.IntStojnost),
    Room=R.RoomName,
    RoomClass=max(CL.ShortName),
    TotalBeds=max(R.NumBeds),
    NumPeople=count(distinct(Guest)),
    Status=(if(max(IsFull) = 1) or(TotalBeds-NumPeople <= 0) then 'Запълнена' else 'Незапълнена' endif),
    UnUsedBeds=if(Max(IsFull) = 1 and TotalBeds-NumPeople < 0) then 0 else if(Max(IsFull) = 1 and TotalBeds-NumPeople >= 0) then TotalBeds-NumPeople else 0 endif endif, --v.2
    FreeBeds=(if((TotalBeds-NumPeople <= 0) or(max(IsFull) = 1)) then 0 else TotalBeds-NumPeople-UnUsedBeds endif),
    PercentUsed=if TotalBeds = 0 then 0.0 else Round((convert(numeric(12,4),(NumPeople+UnusedBeds))/TotalBeds)*100,2) endif,
    Overbooked=if(TotalBeds-NumPeople) < 0 then abs(TotalBeds-NumPeople) else 0 endif from --v.2
    DBA.Rooms as R left outer join(dba.Book as B join dba.Guests as G on B.Guest = G.GuestNum) on
    B.NumLodgings <> 0 and R.RoomNum = B.Room and(
    ((B.Staying = 1)) or(
    (B.Staying = 2) and(days(B.DateRegDt,B.NumLodgings) <> bvrData2)) or(
    (B.Staying = 3) and(days(B.DateRegDt,B.NumLodgings) <> bvrData2)) or(
    (B.Staying = 3) and(days(B.DateRegDt,B.NumLodgings) = bvrData2) and(B.RegNum = (select Max(B2.RegNum) from dba.Book as B2 where B2.Guest = G.GuestNum)))) and
    B.DateRegDt <= bvrData2 and days(B.DateRegDt,B.NumLodgings) > bvrData2,
    DBA.Classes as CL where
    R.RoomCat = CL.Class and
    R.Status <> 3 and
    R.Status <> 10
    group by R.RoomName order by
    Room asc
-- v1[29.09.2009] - Milko - Dobavena kolona procentna zaetost
-- v2[31.03.2010] - Milko - Dobavena kolona Overbooked (nastaneni studenti nad limita)