IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_Kamerierki'))) THEN
Drop View v_Kamerierki
END IF

GO

create view
  "DBA".v_Kamerierki
  as select RoomType=Max(Classes.ShortName),
    RoomNum=Rooms.RoomNum,
    GuestNo=Min(Book.Guest),
    RoomStatus=Max(RoomStatus.Description),
    Elderly=Sum(if(Book.DateRegDt<=bvrData1)
    and(Days(Book.DateRegDt,Book.NumLodgings)>=bvrData1)
    and(Guests.AdultType=0)
    and(Guests.Status=1) then 1 else 0 endif),
    Children=Sum(if(Book.DateRegDt<=bvrData1)
    and(Days(Book.DateRegDt,Book.NumLodgings)>=bvrData1)
    and(Guests.AdultType=1)
    and(Guests.Status=1) then 1 else 0 endif),
    RegDate=GetLeaveArriveDate(1,GuestNo),
    DaysClean=max(Registers.IntStojnost),
    Clean=(if Mod("date"(bvrData1)-RegDate,DaysClean)<>0 then ' ' else 'П,' endif),
    ChangeSheets=(if Mod("date"(bvrData1)-RegDate,(select Max(r.IntStojnost)
      from "dba".Registers as r
      where r.RegisterName='CleanPerDays'))<>0 then '' else 'СБ' endif),
    ToDo=Clean+LeaveStat,
    LeaveDate=GetLeaveArriveDate(2,GuestNo),
    GuestName=Max(if(Book.DateRegDt<=bvrData1)
    and(Days(Book.DateRegDt,Book.NumLodgings)>=bvrData1) then
      Guests."Name"
    else null
    endif),VIP=Max(if(Book.DateRegDt<=bvrData1)
    and(Days(Book.DateRegDt,Book.NumLodgings)>=bvrData1) then
      VIP.VipName else null endif),
    LeaveStat=if LeaveDate=bvrData1 then 'З' else ' ' endif,
    IsReserve=Max(if RP.ReserveNo<>0 then
       if (select Lr.Status from Reserve Lr  where Lr.ReserveNo=RP.ReserveNo)=2 then null else 'Резервирана' endif
    else null endif)
    from "dba".Book left outer join "dba".Guests on Book.Guest=Guests.GuestNum
    ,"dba".Rooms left outer join "dba".ReservePlan as RP on RP.FromDateDt=bvrData1 and Rooms.RoomNum=RP.RoomNo
    ,"dba".Rooms left outer join "dba".Book on Rooms.RoomNum=Book.Room
    and Book.DateRegDt<bvrData1
    and Days(Book.DateRegDt,Book.NumLodgings)>=bvrData1
    and Book.NumLodgings>0
    ,"dba".Guests left outer join "dba".VIP on Guests.TipVip=VIP.N
    ,"dba".Guests,"dba".Classes,"dba".RoomStatus,"dba".VIP,"dba".Registers
    where Rooms.RoomCat=Classes.Class
    and Rooms.Status=RoomStatus.RoomStatusNo
    and Registers.RegisterName='Kamerierki'
    group by RoomNum
//[24.08.2005] Jorko, Fix v LeaveDate. RegDate