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=Guest,GuestName=min(G.Name),
    ArriveDate=Min(Book.DateRegDt),
    LastRoom=MAX(if Book.Staying = 2 then 0 else Book.Room endif),
    --Maria
    LastRoomName=MAX(if Book.Staying = 2 then R.RoomName 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 where
    --Maria
    Book.Room = R.RoomNum and
    --Maria
    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 Guest,RegPrice,PriceKind,PriceKindType
//[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