Forum ERP XL

Kod: Zaznacz cały

CREATE PROCEDURE raportrw
AS
BEGIN
    DECLARE @DynamicPivotQuery NVARCHAR(MAX);
    DECLARE @ColumnNames NVARCHAR(MAX);
    DECLARE @Years NVARCHAR(MAX);

    SET @Years = '2025';
    
    DECLARE @Periods TABLE (
        Period NVARCHAR(20)
    );

    INSERT INTO @Periods (Period)
    SELECT DISTINCT
        CONCAT(YEAR(DATEADD(day, TraNag.TrN_Data2, '18001228')), '_Tydz_', FORMAT(DATEPART(WEEK, DATEADD(day, TraNag.TrN_Data2, '18001228')), '00')) AS Period
    FROM 
        CDN.TraElem AS TraElem
    INNER JOIN 
        CDN.TraNag AS TraNag
    ON 
        TraNag.TrN_GIDNumer = TraElem.TrE_GIDNumer
    WHERE 
        TraElem.TrE_GIDTyp = 1616
        AND TraElem.TrE_GIDFirma = 129121
        AND YEAR(DATEADD(day, TraNag.TrN_Data2, '18001228')) IN (2025);

    SET @ColumnNames = STUFF((
        SELECT 
            ',[' + Period + ']'
        FROM 
            @Periods
        ORDER BY 
            Period -- Sortowanie alfabetyczne
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

    SET @DynamicPivotQuery = '
        SELECT 
            TrE_TwrKod AS [Kod produktu],
            TrE_TwrNazwa AS [Nazwa produktu], 
            ' + @ColumnNames + '
        FROM (
            SELECT 
                TraElem.TrE_TwrKod,
                TraElem.TrE_TwrNazwa,
                CONCAT(YEAR(DATEADD(day, TraNag.TrN_Data2, ''18001228'')), ''_Tydz_'', FORMAT(DATEPART(WEEK, DATEADD(day, TraNag.TrN_Data2, ''18001228'')), ''00'')) AS Period,
                SUM(TraElem.TrE_Ilosc) AS TrE_Ilosc
            FROM 
                CDN.TraElem AS TraElem
            INNER JOIN 
                CDN.TraNag AS TraNag
            ON 
                TraNag.TrN_GIDNumer = TraElem.TrE_GIDNumer
            WHERE 
                TraElem.TrE_GIDTyp = 1616
                AND TraElem.TrE_GIDFirma = 129121
                AND YEAR(DATEADD(day, TraNag.TrN_Data2, ''18001228'')) IN (' + @Years + ')
            GROUP BY 
                TraElem.TrE_TwrKod,
                TraElem.TrE_TwrNazwa,
                YEAR(DATEADD(day, TraNag.TrN_Data2, ''18001228'')),
                DATEPART(WEEK, DATEADD(day, TraNag.TrN_Data2, ''18001228''))
        ) AS SourceTable
        PIVOT (
            SUM(TrE_Ilosc) 
            FOR Period IN (' + @ColumnNames + ')
        ) AS PivotTable
        ORDER BY [Kod produktu], [Nazwa produktu];
    ';

    BEGIN TRY
        EXEC sp_executesql @DynamicPivotQuery;
    END TRY
    BEGIN CATCH
        -- Obsługa błędów
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;
END;