IF (EXISTS(Select * from systable where LCase(table_name) = LCase('v_ReportServices')))
  THEN Drop View v_ReportServices
END IF

Go

Create View v_ReportServices
as
select
  Articles.InvoiceNo,
  Service=Services.ServiceName,
  Suma=round(if Articles.TipDDS = 3 then Articles.TotalSum else Articles.TotalSum*(1+VR.Rate) endif,4),
  Invoices.InvoiceDateDt,
  PayTypes.PayTypeName,
  Admin.FullName,
  ContractName=Contracts.FullName,
  Qty=(if Invoices.CancelInvoiceNo > 0 then(-1)*Articles.Qty else Articles.Qty endif) 
from
  DBA.Articles,
  DBA.PayTypes,
  DBA.Invoices,
  DBA.Services,
  DBA.Admin,
  DBA.Book,
  DBA.Guests,
  DBA.Contracts,
  DBA.VATRates as VR 
where
  Articles.InvoiceNo = Invoices.InvoiceNo
  and Invoices.InvoiceDateDt >= bvrData1
  and Invoices.InvoiceDateDt <= bvrData2
  and Invoices.PayHow = PayTypes.PayTypeN 
  and Articles.ServiceNo = Services.ServiceNo 
  and Invoices.KodAdmin = Admin.AdminNo
  and abs(Articles.RegNo) = Book.RegNum 
  and Articles.RegNo <> 0 
  and Book.Guest = Guests.GuestNum 
  and Guests.Contract *= Contracts.ContractNo
  and VR.ID=2 
union all
select 
  Articles.InvoiceNo,
  Service=Services.ServiceName,
  Suma=round(if Articles.TipDDS = 3 then CalcPriceWithDiscount(Articles.Discount,Articles.Price*Articles.Qty) else CalcPriceWithDiscount(Articles.Discount,Articles.Price*Articles.Qty)*(1+VR.Rate) endif,4),
  Invoices.InvoiceDateDt,
  PayTypes.PayTypeName,
  Admin.FullName,
  ContractName=Contracts.FullName,
  Qty=(if Invoices.CancelInvoiceNo > 0 then(-1)*Articles.Qty else Articles.Qty endif) 
from
  DBA.Articles,
  DBA.PayTypes,
  DBA.Invoices,
  DBA.Services,
  DBA.Admin,
  DBA.ServiceCredits,
  DBA.Guests,
  DBA.Contracts,
  DBA.VATRates as VR 
where
  Articles.InvoiceNo = Invoices.InvoiceNo
  and Invoices.InvoiceDateDt >= bvrData1
  and Invoices.InvoiceDateDt <= bvrData2 
  and Invoices.PayHow = PayTypes.PayTypeN 
  and Articles.ServiceNo = Services.ServiceNo 
  and Invoices.KodAdmin = Admin.AdminNo
  and Articles.CreditNo = ServiceCredits.CreditNo 
  and Articles.CreditNo <> 0 
  and ServiceCredits.GuestNo = Guests.GuestNum 
  and Guests.Contract *= Contracts.ContractNo
  and VR.ID=2 
union all
select
  InvoiceNo=max(Articles.InvoiceNo),
  Service=max(Services.ServiceName),
  Suma=round(max(if Articles.TipDDS = 3 then Articles.TotalSum else Articles.TotalSum*(1+VR.Rate) endif),4),
  max(Invoices.InvoiceDateDt),
  max(PayTypes.PayTypeName),
  max(Admin.FullName),
  ContractName=max(Contracts.FullName),
  Qty=(if Invoices.CancelInvoiceNo > 0 then(-1)*Articles.Qty else Articles.Qty endif) 
from
  DBA.Articles,
  DBA.PayTypes,
  DBA.Invoices,
  DBA.Services,
  DBA.Admin,
  DBA.ResMoney,
  DBA.Guests,
  DBA.Contracts,
  DBA.VATRates as VR
where
  Articles.InvoiceNo = Invoices.InvoiceNo 
  and Invoices.InvoiceDateDt >= bvrData1
  and Invoices.InvoiceDateDt <= bvrData2
  and Invoices.PayHow = PayTypes.PayTypeN
  and Articles.ServiceNo = Services.ServiceNo 
  and Invoices.KodAdmin = Admin.AdminNo
  and Articles.RegNo = 0
  and Articles.CreditNo = 0 
  and ResMoney.InvoiceNo = Invoices.InvoiceNo
  and Guests.GuestNum = ResMoney.GuestNo 
  and Contracts.ContractNo = Guests.Contract 
  and Articles.ServiceNo in (1004,1005)
  and VR.ID=2
group by
  Articles.InvoiceNo,
  Articles.ArticleNo,
  Articles.Qty,
  Invoices.CancelInvoiceNo
union all
select 
  VA.InvoiceNo,
  Service=S.ServiceName,
  Suma=Round(VA.GrandTotal*(1+VR.Rate),4),
  VI.InvoiceDateDt,
  PT.PayTypeName,
  Admin.FullName,
  ContractName=Contracts.FullName,
  Qty=(if VI.CancelInvoiceNo > 0 then -Abs(VA.TotalNights) else Abs(VA.TotalNights) endif) 
from
  DBA.VaucherArticles as VA,
  DBA.VaucherInvoices as VI,
  DBA.PayTypes as PT,
  DBA.VATRates as VR,
  DBA.Services as S,
  DBA.Admin,
  DBA.Contracts
where
  VA.InvoiceNo = VI.VaucherInvoiceNo
  and VI.PayHow = PT.PayTypeN
  and VI.KodAdmin = Admin.AdminNo
  and Contracts.ContractNo = VI.ContractNo
  and VI.InvoiceDateDt >= bvrData1
  and VI.InvoiceDateDt <= bvrData2
  and VR.ID=4
  and VA.ServiceNo=S.ServiceNo