root/trunk/SQL/Views/ZAMOWIENIA_REKLAMY.txt


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[ZAMOWIENIA_REKLAMY] AS
SELECT 
z.idZamowienia,
COUNT(r.ReklamaID) AS iloscReklam, 
ROUND(SUM((r.[CENA JEDN] - r.[RABAT WARTOŚĆ]) * r.krotność * (1 + wyroznienie_procent)), 2) AS wartoscNetto, 
SUM(r.Brutto_Euro) AS kwotaWalutaBrutto, 
r.Brutto_Euro_Miano
FROM dbo.zamowienia AS z 
LEFT JOIN dbo.REKLAMA AS r ON z.idZamowienia = r.idZamowienia
WHERE r.Brutto_Euro_Miano is null or r.Brutto_Euro_Miano =''
GROUP BY z.idZamowienia,r.Brutto_Euro_Miano
UNION 
SELECT     
z.idZamowienia,
COUNT(r.ReklamaID) AS iloscReklam, 
ROUND(SUM(r.Brutto_Euro*r.waluta_kurs/(r.waluta_Przelicznik*(1+r.VAT))),2) AS wartoscNetto, 
SUM(r.Brutto_Euro) AS kwotaWalutaBrutto, 
r.Brutto_Euro_Miano
FROM dbo.zamowienia AS z 
LEFT JOIN dbo.REKLAMA AS r ON z.idZamowienia = r.idZamowienia
where NOT (r.Brutto_Euro_Miano IS NULL OR r.Brutto_Euro_Miano ='')
GROUP BY z.idZamowienia,r.Brutto_Euro_Miano
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO