delete from SvoServicesStn where Svoistvo = 11

go

if (select count(*) from SvoServicesStn where Svoistvo=11 and Glava = 1014)=0 then
  insert into SvoServicesStn
    VALUES(1019, 11 ,'')
end if;

go

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) then TotalBeds-NumPeople else 0 endif),
    FreeBeds=(if((TotalBeds-NumPeople <= 0) or(max(IsFull) = 1)) then 0 else TotalBeds-NumPeople endif),
    PercentUsed=if TotalBeds = 0 then 0.0 else Round((convert(numeric(12,4),(NumPeople+UnusedBeds))/TotalBeds)*100,2) endif from
    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