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

GO

create view
  DBA.v_BusyRoomsByPeriod as
  select RoomTypes=ShortName,BusyNum=DBA.BusyRoomsForDate(bvrData1,bvrData2,cl.class),
  --Maria +, Status na staia za period - izvajdat se staite ot suotvetnia tip, koito za perioda imat takuv status, che ne sa za prodajba
    AvailForOneDay=(select count(*) from dba.rooms as r,dba.classes as c where r.RoomCat = c.class and r.status <> 1 and r.status <> 3 and r.status <> 10 and c.class = cl.class)*numdays - (select count(distinct R.RoomNum)
     from RoomStatusForPeriod as RSFP, 
          Classes as C, Rooms as R 
          where StartDate<=bvrData2 and
                EndDate>=bvrData1
          and NStatus<>0 and NStatus<>2 
          and R.RoomCat=C.Class 
          and R.RoomNum=RSFP.NRoom
          and C.Class=cl.Class),
  --Maria -
    numdays=(bvrData2-bvrData1)+1,
  --Maria +
    AvailForPeriod=(select count(*) from dba.rooms as r,dba.classes as c where r.RoomCat = c.class and r.status <> 1 and r.status <> 3 and r.status <> 10 and c.class = cl.class)*numdays-BusyNum - (select count(distinct R.RoomNum)
     from RoomStatusForPeriod as RSFP, 
          Classes as C, Rooms as R 
          where StartDate<=bvrData2 and
                EndDate>=bvrData1
          and NStatus<>0 and NStatus<>2 
          and R.RoomCat=C.Class 
          and R.RoomNum=RSFP.NRoom
          and C.Class=cl.Class),
  --Maria -
    Persent=if AvailForOneDay = 0 then
      if BusyNum = 0 then
        0
      else
        100
      endif
    else
      ((100*BusyNum)/AvailForOneDay)
    endif from
    dba.classes as cl where
    valid <> 0
    --Maria +, dobaveno grupirane
    group by cl.Class, cl.ShortName
    --Maria -
  union
  select RoomTypes='Тотал',BusyNum=sum(DBA.BusyRoomsForDate(bvrData1,bvrData2,cl.class)),
  --Maria +
    AvailForOneDay=(select count(*) from dba.rooms as r,dba.classes as c where r.RoomCat = c.class and r.status <> 1 and r.status <> 3 and r.status <> 10)*numdays - (select count(distinct RSFP.NRoom)
     from RoomStatusForPeriod as RSFP, Rooms as R 
          where StartDate<=bvrData2 and
                EndDate>=bvrData1
          and NStatus<>0 and NStatus<>2  
          and R.RoomNum=RSFP.NRoom),
 --Maria -
    numdays=(bvrData2-bvrData1)+1,
 --Maria +
    AvailForPeriod=(select count(*) from dba.rooms as r,dba.classes as c where r.RoomCat = c.class and r.status <> 1 and r.status <> 3 and r.status <> 10)*numdays-BusyNum-(select count(distinct R.RoomNum)
     from RoomStatusForPeriod as RSFP,  Rooms as R 
          where StartDate<=bvrData2 and
                EndDate>=bvrData1
          and NStatus<>0 and NStatus<>2 
          and R.RoomNum=RSFP.NRoom),
 --Maria -
    Persent=((100*BusyNum)/AvailForOneDay) from dba.classes
    as cl where valid <> 0