SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[FakturyPoznanKatowice] AS
SELECT FakturyPozKat.* FROM
(
SELECT DISTINCT F.id_faktury AS ID,
CONVERT(VARCHAR,F.NUMER) + '/' + CONVERT(VARCHAR,F.NUMER_ROZ) + '/' + CONVERT(VARCHAR,F.NUMER_ROK) AS NrFaktury,
F.[DATA_SPRZEDAZY] AS DataSprzedazy,
F2.ID_FAKTURY AS IDPozKat,
CONVERT(VARCHAR,F2.NUMER) + '/' + CONVERT(VARCHAR,F2.NUMER_ROZ) + '/' + CONVERT(VARCHAR,F2.NUMER_ROK) AS NrFakturyPozKat,
DATEPART(week, F2.DATA_WYSTAWIENIA) AS NrTygodnia,
SUM(FD.NETTO) AS Netto,
SUM(FD2.NETTO) AS NettoPozKat,
F2.[DATA_WYSTAWIENIA] AS DataWystawieniaPozKat
FROM FAKTURA_DETAILS FD 
LEFT JOIN faktury F ON F.id_faktury = FD.id_faktury 
LEFT JOIN FAKTURA_DETAILS FD2 ON FD.reklamaId = FD2.reklamaId AND FD.NR_WYDANIA = FD2.NR_WYDANIA
LEFT JOIN FAKTURY F2 ON FD2.id_Faktury = F2.id_faktury
WHERE F.NUMER_ROZ = 'POZ' OR F.NUMER_ROZ='KAT'
GROUP BY F.id_faktury, F.NUMER, F.NUMER_ROZ, F.NUMER_ROK, F.[DATA_SPRZEDAZY], F2.ID_FAKTURY, F2.NUMER, F2.NUMER_ROZ, F2.NUMER_ROK, F2.DATA_WYSTAWIENIA
) FakturyPozKat
WHERE ID <> IDPozKat
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO