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=Rooms.RoomName,
    RoomClass=Max(CL.ShortName),
    TotalBeds=max(Rooms.NumBeds),
    NumPeople=count(Book.RegNum),
    Status=(if(max(IsFull) = 1) or(TotalBeds-NumPeople <= 0) then 'Запълнена' else 'Незапълнена' endif),
	UnUsedBeds=(if (max(IsFull) = 1) then TotalBeds-NumPeople else 0 endif),
    FreeBeds=(if ((TotalBeds-NumPeople < 0) or (max(IsFull) = 1)) then 0 else TotalBeds-NumPeople endif)
from
    DBA.Rooms left outer join dba.Book on Book.NumLodgings <> 0 and
    Rooms.RoomNum = Book.Room and
    Days(Book.DateRegDt,Book.NumLodgings) >= bvrData2 and
    Book.DateRegDt <= bvrData2,
    DBA.Classes as CL where
    Rooms.RoomCat = CL.Class and
    Rooms.Status <> 1 and
    Rooms.Status <> 3 and
    Rooms.Status <> 10
    group by Rooms.RoomName order by
    Room asc
go

if (not exists(select N from Relations where TableName='v_FreeBeds' and ColName='UnUsedBeds')) then
  INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
     VALUES('v_FreeBeds','UnUsedBeds','Свободни и заети легла','Неизползваеми','Free and occupied beds','UnUsedBeds',2,GetFreeNInRelations('Free and occupied beds', null, null, 2),'SUM(UnUsedBeds)','Sum(qrData.Неизползваеми)','',0,0,'','','','',80,'',0,0,1,today(*))
end if	 

Go