root/trunk/SQL/Views/VIEW_RAPORT_ZMIAN_EMISJI.txt @ 278

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER view [dbo].[VIEW_RAPORT_ZMIAN_EMISJI] as
SELECT T.idFaktury,
CAST(F.numer AS NVARCHAR(3)) + '/' + CAST(F.numer_roz AS NVARCHAR(3))  + '/' + CAST(F.numer_rok AS NVARCHAR(4)) AS NrFaktury,
T. reklamaId, 
R.[id reklamy],
Z.idZamowienia, 
T.nrWyd, 
T.dataZmiany, 
T.netto, 
NR.DATA_W, 
T.Korekta FROM 
(
SELECT U.idFaktury, U.reklamaId, ZE.idEmisji, ZE.popNrWyd AS nrWyd, ZE.dataZmiany, - U.netto AS netto, 0 AS Korekta 
FROM dbo.zmianyEmisji ZE 
LEFT JOIN dbo.[UKA¯E SIÊ W NR] U ON ZE.idEmisji = U.id 
WHERE ZE.popNrWyd > 0
UNION 
select U.idFaktury,U.reklamaId, ZE.idEmisji, ZE.nastNrWyd, ZE.dataZmiany, + U.netto, 0 as Korekta 
FROM dbo.zmianyEmisji ZE 
LEFT JOIN dbo.[UKA¯E SIÊ W NR] U ON ZE.idEmisji = U.id 
WHERE ZE.nastNrWyd > 0
UNION 
SELECT idFaktury, reklamaId, ID, [Nr Wydania], dataAnulowania, -netto, 1 as Korekta 
FROM dbo.[UKA¯E SIÊ W NR]
WHERE status=2 AND idFaktury IS NOT NULL AND [Nr Wydania] > 0
) T
LEFT JOIN dbo.REKLAMA R ON T.reklamaid = R.reklamaid
LEFT JOIN zamowienia Z ON Z.idZamowienia = R.idZamowienia
LEFT JOIN faktury F ON F.id_faktury = T.IdFaktury
LEFT JOIN klienci K ON Z.idKlienta = K.customerId
LEFT JOIN NR ON NR.TYT=R.tytu³ AND NR.NRW=T.nrWyd
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO