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

GO

create view v_RoomStatusForData2 as
select distinct NRoom
from RoomStatusForPeriod
where StartDate <= bvrData2 and EndDate >= bvrData2 and NStatus <> 0
order by NRoom

GO

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

GO

create view v_FreeRooms as
select Room=Rooms.RoomNum,
    Class=Max(CL.ShortName),
    Status=(if count(Book.RegNum) = 0 then '  Свободна' else
      if min(Book.DateRegDt+Book.NumLodgings) = bvrData2 then 'Напуска' else ' Заета' endif
    endif),
    ReserveName=max(Reserve.Name) 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 left outer join
    DBA.ReservePlan on Rooms.RoomNum = ReservePlan.RoomNo and
    ReservePlan.FromDateDt = bvrData2 left outer join
    DBA.Reserve on ReservePlan.ReserveNo = Reserve.ReserveNo,
    DBA.Classes as CL,
    DBA.Reserve, 
	RoomStatusForPeriod RSFP where
    Rooms.RoomCat = CL.Class and 
    Rooms.Status <> 1 and
    Rooms.Status <> 3 and
    Rooms.Status <> 10 and (Room not in (select * from v_RoomStatusForData2))
    group by Rooms.RoomNum
    order by Room