root/trunk/SQL/StoredProcedures/sp_adMotoRaportProwizja.txt @ 352

-- =============================================
-- Create basic stored procedure template
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'sp_adMotoRaportProwizja' 
)
   DROP PROCEDURE dbo.sp_adMotoRaportProwizja
GO

CREATE PROCEDURE dbo.sp_adMotoRaportProwizja
        @rok int,
        @miesiac int,
        @tytul nvarchar(100)
AS

DECLARE @Wplywy TABLE
(
        IdFaktury INT,
        NumerFaktury INT,
        Agent nvarchar(100),
        SymbolFaktury nvarchar(100),
        DataWplaty DATETIME,
        Netto MONEY,
        DataFaktury DATETIME,
        Prowizja MONEY,
        Agencja nvarchar(100)
)
DECLARE @Sprzedaz TABLE
(
        IdFaktury INT,
        NumerFaktury INT,
        Agent nvarchar(100),
        SymbolFaktury nvarchar(100),
        DataWplaty DATETIME,
        Netto MONEY,
        DataFaktury DATETIME,
        Prowizja MONEY,
        Agencja nvarchar(100)
)

INSERT INTO @Wplywy
SELECT 
ID_FAKTURY AS IdFaktury, 
NumerFaktury, 
LOWER(Symbol) AS Agent, 
NR_FK AS SymbolFaktury, 
dataWplaty AS DataWplaty, 
NETTO AS Netto, 
DATA_WYSTAWIENIA AS DataFaktury, 
ROUND(NETTO * procentProwizji, 2) AS Prowizja, 
Agencja
FROM WplywyByTytul
WHERE (YEAR(dataWplaty) = @rok) 
AND (MONTH(dataWplaty) = @miesiac) 
AND (Tytul IN (@tytul))

UPDATE @Wplywy
SET Netto = NULL
WHERE MONTH(DataFaktury)<@miesiac

INSERT INTO @Sprzedaz
SELECT 
Id AS IdFaktury, 
Numer AS NumerFaktury,
Agent,
NumerFaktury AS SymbolFaktury,
NULL AS DataWplaty,
Netto,
Data AS DataFaktury,
NULL AS Prowizja,
Agencja
FROM FakturyNettoByTytul
WHERE
(YEAR(Data) = @rok) 
AND (MONTH(Data) in (@miesiac))
AND (Tytul in (@tytul))

SELECT * FROM @Wplywy 
UNION
SELECT * FROM @Sprzedaz
WHERE IdFaktury NOT IN (SELECT IdFaktury FROM @Wplywy)
ORDER BY DataWplaty, Agent, NumerFaktury

        
GO

-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.sp_adMotoRaportProwizja 2009, 1, 'adMot'
GO