if (Exists (Select Table_name from SysTable where Table_name='v_UsedServicesByContract')) then
  Drop View v_UsedServicesByContract
end if

GO

CREATE VIEW "DBA"."v_UsedServicesByContract"  as select distinct g.GuestNum as GNum,
    g."Name" as Guest,
    b.DateRegDt as DateCredit,
    (if b.IsRest=0 then 'Нощувка в стая '+cast(b.Room as varchar) else 'Почивка в стая '+cast(b.Room as varchar) endif) as Service,
    b.NumLodgings as Quantity,
    b.Price as PrWithoutDisc,
    cur.Symbol as curKod,
    Quantity*PrWithoutDisc as SumWithoutDisc,
    scr.Discount as Discount,
    CalcPriceWithDiscount(Discount,PrWithoutDisc) as PrWithDisc,
    Quantity*PrWithDisc as SumWithDisc,
    SumWithDisc*GetCurrencyRate(DateCredit,cur.KodCurrency) as BGSumWithDisc,
    c.FullName as Dogovor,
    b.Room as RoomNum
    from "dba".Book as b,"dba".Guests as g,"dba".Contracts as c,"dba".Currencies as cur,"dba".ServicesForContract as scr
    where b.ContractPays=c.ContractNo
    and b.DateRegDt between bvrData1 and bvrData2
    and b.Guest=g.GuestNum
    and b.ContractPays=scr.ContractNo
    and scr.ServiceNo in(-1,-2)
    and b.KodCurrency=cur.KodCurrency
    and c.ContractNo<>0 union all
  select g.GuestNum as GNum,
    g."Name" as Guest,
    sc.DateCreditDt as DateCredit,
    s.ServiceName,
    (select Sum(NumberServices) from "dba".ServiceCredits
      where Service=s.ServiceNo and GuestNo=g.GuestNum
      and KodCurrency=cur.KodCurrency
      and CreditNo=sc.CreditNo) as Quantity,
    sc.SumCredit as PrWithoutDisc,
    cur.Symbol as curKod,
    (Quantity*PrWithoutDisc) as SumWithoutDisc,
    (select Discount from "dba".ServicesForContract where ServiceNo=s.ServiceNo and ContractNo=c.ContractNo) as Discount,
    CalcPriceWithDiscount(Discount,PrWithoutDisc) as PrWithDisc,
    Quantity*PrWithDisc as SumWithDisc,
    SumWithDisc*GetCurrencyRate(DateCredit,cur.KodCurrency) as BGSumWithDisc,
    c.FullName as Dogovor,
    sc.RoomNum
    from "dba".ServiceCredits as sc,"dba".Contracts as c,"dba".Guests as g,"dba".Services as s,"dba".Currencies as cur
    where sc.ContractPays=c.ContractNo
    and sc.DateCreditDt between bvrData1 and bvrData2
    and sc.GuestNo=g.GuestNum
    and sc.ContractPays<>0
    and s.ServiceNo=sc.Service
    and sc.KodCurrency=cur.KodCurrency
// v[5.09.2003 ] Jorko opravia dublirane na zapisi.
// v[12.08.2005] Jorko mahnah distinct ot vtoria select v rezultat moze da se poiaviat ednakvi redove za uslugite