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)),
    TotPeople=convert(decimal,count(distinct B.Guest)),
    NumPeople=convert(decimal,count(distinct(if(dateadd(dd,B.NumLodgings,B.DateRegDt) > bvrData2) /*and(B.Staying <> 3)*/ then B.Guest else null endif))),
    OverduePeople=TotPeople-NumPeople,
    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),
    FreeBeds=(if((TotalBeds-TotPeople <= 0) or(max(R.IsFull) = 1)) then convert(decimal,0) else convert(decimal,(TotalBeds-TotPeople-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(dateadd(dd,B.NumLodgings,B.DateRegDt) <> bvrData2)) or
    ((B.Staying = 3) and(dateadd(dd,B.NumLodgings,B.DateRegDt) <> bvrData2)) or
    ((B.Staying = 3) and(dateadd(dd,B.NumLodgings,B.DateRegDt) = 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) /*and(B.Staying <> 3)*/) or(B.Staying = 1)),
    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)
-- v3[25.03.2015] - Nataliya - Dobavena kolona OverduePeople (nastaneni s prosrochen srok) -> promqna v iz`islenieto na nastanenite i na svobodnite legla