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

GO

create view DBA.v_NewArriveStayLeaveGuestBook
  as select GuestNum=Book.Guest,GuestName=min(G.Name),
    ArriveDate=Min(Book.DateRegDt),
    LastRoom=MAX(if Book.Staying = 1 then Book.Room else Book.Room endif),
    --Maria
    LastRoomName=R.RoomName, --MAX(if Book.Staying = 2 then null else R.RoomName endif),
    --Maria
    NumNights=Sum(Book.NumLodgings),
    LeaveDate=Days(ArriveDate,NumNights),
    -- Yanko +
    --    RegPrice=max(Book.Price||' '||C.Symbol),
    RegPrice=Book.Price || ' ' || C.Symbol,
    -- Yanko -
    //PayNightPrice=sum(Book.PayedLodgings*Book.Price*DR.ExchangeRate),
    Admin=Max(Admin.FullName),
    PriceKind=PRK.Description,
    PriceKindType=PKT.Name
    from DBA.Book,DBA.Admin,dba.Guests as G,/*dba.DayRates as DR,*/dba.Rooms as R /*Maria*/ /*Maria*/
      ,dba.Currencies as C,DBA.PriceKinds as PRK,DBA.PriceKindTypes as PKT
    --Maria
    where Book.Room = R.RoomNum
    --Maria
    and Book.KodAdmin = Admin.AdminNo
    and Book.IsRest <> 1
    and Book.Guest = G.GuestNum
    /*and DR.KodCurrency = Book.KodCurrency
    and DR.DateDt = Book.DateRegDt*/
    and C.KodCurrency = Book.KodCurrency
    and Book.PriceKind = PRK.PriceKind
    and PKT.N = PRK.PriceKindType --and Book.Staying in (1, 3)
    group by Book.Guest,RegPrice,PriceKind,PriceKindType,LastRoomName,book.RegNum
//[v. 22.06.2006]
//[v. 29.05.2008] - Maria, dobavena kolona PriceKind
//[v 27.10.2009] Milko dobavena kolona PriceKindType
//[v 18.02.2010] Milko - Promenena kolona LastRoomName da vadi nomera na poslednata staq
//[v 28.09.2010] Milko - Promenena kolona LastRoomName da vadi nomera na poslednata staq
//v [28.09.2010] Milko - Promenena kolona LastRoomName slojena v grupirane