moodle.waes.ac.uk/moodle.waes.ac.uk.zip/moodle.waes.ac.uk/TMG/SP1-TMG-KB981324-AMD64-ENU.msp/PCW_CAB_SHFx2/F2143_msfpcui.dll/BINARY/25178

Text File | 2010-06-15 | 3KB | 84 lines

CREATE PROCEDURE [dbo].[ISA_spTrimArrayDestinationSummary]
    @FromDate datetime,
    @ToDate   datetime
AS
BEGIN
    SET NOCOUNT ON;
    IF OBJECT_ID ('tblDestinationSummary_Daily', 'u') IS NULL
        RETURN
    -- create #table1 empty table copy
    SELECT TOP(0) * INTO #Table1 FROM [tblDestinationSummary_Daily]
    DELETE FROM [tblDestinationSummary_Daily]
    OUTPUT DELETED.* INTO #Table1
    WHERE ([Date] >= @FromDate) AND ([Date] < @ToDate)
    -- 1st aggregate the array
    SELECT
        [DestinationIP],
        SUM([BytesIn]) AS BytesIn,
        SUM([BytesOut]) AS BytesOut,
        SUM([TotalBytes]) AS TotalBytes,
        SUM([Requests]) AS Requests,
        [Date] AS Date
    INTO #TableOrig
    FROM #Table1
    GROUP BY
        [DestinationIP], [Date]
    TRUNCATE TABLE #Table1
    -- now calculate the new TOPs
    DECLARE @SQLString1 nvarchar(2000) =
    'SELECT *
    FROM (
        SELECT
            [DestinationIP], [BytesIn], [BytesOut], [TotalBytes], [Requests], [Date]
        FROM (
            SELECT
                [DestinationIP], [BytesIn], [BytesOut], [TotalBytes], [Requests], [Date],
                ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY '
    DECLARE @SQLString2 nvarchar(2000) =
                    ' DESC) AS rownum
            FROM #TableOrig
            WHERE [DestinationIP] != ''00000000-0000-0000-0000-000000000000'' AND '
    DECLARE @SQLString3 nvarchar(2000) =
            ' > 0 ) t
        WHERE rownum <= 5000
    ) ttt
    WHERE [DestinationIP] IS NOT NULL
          AND NOT EXISTS (SELECT * FROM #Table1
                          WHERE ttt.[DestinationIP] = [DestinationIP] AND
                                ttt.[Date] = [Date])'
    DECLARE @SQLString nvarchar(4000)
    DECLARE @OrderString nvarchar(200)
    SET @OrderString = '[TotalBytes]'
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    PRINT @SQLString
    INSERT INTO #Table1
    EXECUTE(@SQLString)
    SET @OrderString = '[Requests]';
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    INSERT INTO #Table1
    EXECUTE(@SQLString)
    SET @OrderString = '[BytesIn]';
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    INSERT INTO #Table1
    EXECUTE(@SQLString)
    SET @OrderString = '[BytesOut]';
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    INSERT INTO #Table1
    EXECUTE(@SQLString)
    ---- now add tail
    INSERT INTO #TABLE1
    SELECT
        '00000000-0000-0000-0000-000000000000', SUM([BytesIn]), SUM([BytesOut]), SUM([TotalBytes]), SUM([Requests]), [Date]
    FROM #TableOrig ttt
    WHERE
        [Requests] IS NOT NULL AND
        NOT EXISTS (SELECT *
                    FROM #Table1
                    WHERE ttt.[DestinationIP] = [DestinationIP] AND
                          ttt.[Date] = [Date])
    GROUP BY [Date]
    INSERT INTO [tblDestinationSummary_Daily]
    SELECT *
    FROM #TABLE1
END