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

GO

create view DBA.v_Blocking
  as select Room=R.RoomNum,
    Class=Max(CL.ShortName),
    /* 'Svobodna' znachi svobodna ot vchera, a ne za dnes:*/
    Status=(if count(Book.RegNum) = 0 then '  Свободна'
    else 'Напуска'
    endif) from DBA.Rooms as R left outer join dba.Book on Book.Staying = 1 and R.RoomNum = Book.Room and Days(Book.DateRegDt,Book.NumLodgings) <= current date,DBA.Classes as CL
    where R.RoomCat = CL.Class and R.Status <> 1 and R.Status <> 3 and R.Status <> 10
    group by R.RoomNum