set bvrSpravkaM = 1
go
set bvrSpravkaY = 2009
go

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

GO

create view
  DBA.v_OwePaidRentMonth
  as select GNo=Guest,
    Month=DBA.ImeMesec(bvrSpravkaM),
    Year=bvrSpravkaY,
    G.Name,
    Uni=U.FullName,
    Kurs=K.Name,
    Spec=S.Name,
    G.FN,
    G.Address,
    City=(select SettleMentName from Settlements where N = G.SettlementN),
    G.Sex,
    PriceKind=PK.Description,
    Usluga=DBA.GetNoshtuvkaNameForMonthBook(B.RegNum),
    Cena=isNull(GetRentPriceForDate(B.RegNum,'01.'||bvrSpravkaM||'.'||bvrSpravkaY),B.Price*C.ExchangeRate),
    Kol=Round(DBA.GetQtyRentForMonth(B.RegNum,0),2),
    SumOwe=Round(DBA.GetQtyRentForMonth(B.RegNum,0)*Cena,2),
    KolPaid=Round(DBA.GetQtyRentForMonth(B.RegNum,1),2),
    SumPaid=Round(DBA.GetQtyRentForMonth(B.RegNum,1)*Cena,2),
    Razlika=SumOwe-SumPaid,
	RealDatePayed=GetRealDatePayedRentForMonth(B.RegNum),
	Koef=Kol,
    BrStudenti=1,
    RName=R.RoomName,
	CountryName=Ctr.Description
from
    DBA.Book as B,DBA.PriceKinds as PK,DBA.Guests as G,DBA.Kursove as K,DBA.Specialities as S,
	DBA.Universities as U,DBA.Currencies as C,DBA.Rooms as R, DBA.Countries as Ctr
where
    B.PriceKind = PK.PriceKind and G.GuestNum = B.Guest and PK.NachinPlat = 1 
	and B.DateRegDt < dateadd(month,1,date('1.'||bvrSpravkaM||'.'||bvrSpravkaY))
	and dateadd(day,B.NumLodgings,B.DateRegDt) > date('1.'||bvrSpravkaM||'.'||bvrSpravkaY)
	and G.Kurs = K.N and G.SpecNo = S.N and G.UniN = U.N and B.KodCurrency = C.KodCurrency and R.RoomNum = B.Room 
	and G.NativeCountry=Ctr.CountryKod
    
Go