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

GO

create view
 v_NewArriveStayLeaveGuestBook
 
 AS

select GuestNum=Guest,GuestName=min(G.Name),
ArriveDate=Min(Book.DateRegDt),
LastRoom=MAX(if Book.Staying = 1 then Book.Room else Book.Room endif),
--Maria
LastRoomName=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 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
//[v 28.09.2010] Milko - Promenena kolona LastRoomName da vadi nomera na poslednata staq


go

update SubsidyNormativ
set PeriodEnd = '31.08.2010'
where Id=3

go

delete from SubsidyNormativ where Id=4

go

insert into SubsidyNormativ (Id, PeriodStart, PeriodEnd, Normativ)
values (4, '01.09.2010', '31.12.2010', 1)