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/25188

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

CREATE PROCEDURE [dbo].[ISA_spTrimArrayProtocolSummary]
    @FromDate datetime,
    @ToDate   datetime
AS
BEGIN
    SET NOCOUNT ON;
    IF OBJECT_ID ('tblProtocolSummary_Daily', 'u') IS NULL
        RETURN
    -- create #table1 empty table copy
    SELECT TOP(0) * INTO #Table1 FROM [tblProtocolSummary_Daily]
    DELETE FROM [tblProtocolSummary_Daily]
    OUTPUT DELETED.* INTO #Table1
    WHERE ([Date] >= @FromDate) AND ([Date] < @ToDate)
    SELECT
        [Protocol] AS Protocol,
        SUM([BytesIn]) AS BytesIn,
        SUM([BytesOut]) AS BytesOut,
        SUM([TotalBytes]) AS TotalBytes,
        SUM([Requests]) AS Requests,
        [LogType] AS LogType,
        [Date] AS Date
    INTO #TableOrig
    FROM #Table1
    GROUP BY
        [Protocol],
        [LogType],
        [Date]
    TRUNCATE TABLE #Table1
    DECLARE @SQLString1 nvarchar(2000) =
    'SELECT *
    FROM (
        SELECT
            [Protocol], [BytesIn], [BytesOut], [TotalBytes], [Requests], [LogType], [Date]
        FROM (
            SELECT
                [Protocol], [BytesIn], [BytesOut], [TotalBytes], [Requests], [LogType], [Date],
                ROW_NUMBER() OVER (PARTITION BY [Date], [LogType] ORDER BY '
    DECLARE @SQLString2 nvarchar(2000) =
                    ' DESC) AS rownum
            FROM #TableOrig
            WHERE [Protocol] != N''{[23124]}'' AND '
    DECLARE @SQLString3 nvarchar(2000) =
            ' > 0) t
        WHERE rownum <= 5000
    ) ttt
    WHERE [Protocol] IS NOT NULL
          AND NOT EXISTS (SELECT * FROM #Table1
                          WHERE ttt.[Protocol] = [Protocol] AND
                                ttt.[LogType] = [LogType] 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
        N'{[23124]}', SUM([BytesIn]), SUM([BytesOut]), SUM([TotalBytes]), SUM([Requests]), [LogType], [Date]
    FROM #TableOrig ttt
    WHERE
        [Requests] IS NOT NULL AND
        NOT EXISTS (SELECT *
                    FROM #Table1
                    WHERE ttt.[Protocol] = [Protocol] AND
                        ttt.[LogType] = [LogType] AND
                        ttt.[Date] = [Date])
    GROUP BY [LogType], [Date]
    INSERT INTO [tblProtocolSummary_Daily]
    SELECT *
    FROM #TABLE1
END