IF (Exists(select Table_name from SysTable where LCase(Table_name)=LCase('v_New_StatisticsByCountry'))) THEN
  Drop view v_New_StatisticsByCountry
END IF

Go

Create View v_New_StatisticsByCountry AS
SELECT DISTINCT
B.Guest, G.NativeCountry, C.Description, G.AdultType, B.ContractPays,
DateReg = (Select Min(DateRegDt) from Book where Guest = G.GuestNum),
NumLodgings = DateIntersection(DateReg,DateReg+(Select Sum(NumLodgings) from Book where Guest = G.GuestNum),bvrData1,bvrData2),
NewLodgings = (if DateReg >= bvrData1 then 1 else 0 endif),
LastDate = (Select Max(DateRegDt) from Book where Guest = G.GuestNum),
LastLodgings = (Select Max(NumLodgings) from Book where Guest = G.GuestNum and DateRegDt=LastDate),
Room = (Select Max(Room) from Book where Guest = G.GuestNum and DateRegDt=LastDate)
FROM Book as B, Guests as G, Countries as C
WHERE
B.Guest = G.GuestNum
AND G.NativeCountry = C.CountryKod
AND G.Status<>0
AND DateIntersection(DateReg,LastDate+LastLodgings,bvrData1,bvrData2)>0
// pomoshtno view za spravkata "statistika po strani"
//[17.August.2006] - Yanko