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

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

CREATE PROCEDURE [dbo].[ISA_spWebProtocolsRDL]
    @FromDate datetime,
    @ToDate datetime,
    @ReportType varchar(10),
    @TopCount int,
    @SortOrder varchar(100)
AS
BEGIN
    DECLARE @SummTableName varchar(100)
    SET @SummTableName = dbo.fnGetSummaryTableName('tblProtocolSummary', @FromDate, @ToDate, @ReportType)
    DECLARE @OrderColumn varchar(100)
    SET @OrderColumn =
            CASE
                WHEN @SortOrder = 'Requests' THEN 'Requests'
                WHEN @SortOrder = 'BytesIn' THEN 'BytesIn'
                WHEN @SortOrder = 'BytesOut' THEN 'BytesOut'
                ELSE 'TotalBytes'
            END;
    CREATE TABLE #Table1
    (
        Protocol nvarchar(128),
        BytesIn bigint,
        BytesOut bigint,
        TotalBytes bigint,
        Requests bigint
    );
    IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
    BEGIN
        DECLARE @strQuery varchar(8000)
        SET @strQuery =
        'SELECT
            [Protocol] AS Protocol,
            SUM([BytesIn]) AS BytesIn,
            SUM([BytesOut]) AS BytesOut,
            SUM([TotalBytes]) AS TotalBytes,
            SUM([Requests]) AS Requests
        FROM ' + @SummTableName + '
        WHERE ([date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
            AND [LogType]  = 1
        GROUP BY [Protocol]
        ORDER BY ' + QUOTENAME(@OrderColumn) + ' DESC;
        '
        INSERT #Table1
        EXEC(@strQuery)
    END
    DECLARE @TotalRequests bigint
    SET @TotalRequests = (SELECT SUM(Requests) FROM #Table1 )
    IF (@TotalRequests = 0)
        SET @TotalRequests = 1
    DECLARE @totalBytes bigint
    SET @totalBytes = (SELECT SUM(TotalBytes) FROM #Table1 )
    IF (@totalBytes = 0)
        SET @totalBytes = 1
    DECLARE @TotalBytesIn bigint
    SET @TotalBytesIn = (SELECT SUM(BytesIn) FROM #Table1)
    IF (@TotalBytesIn = 0)
        SET @TotalBytesIn = 1
    DECLARE @TotalBytesOut bigint
    SET @TotalBytesOut = (SELECT SUM(BytesOut) FROM #Table1)
    IF (@TotalBytesOut = 0)
        SET @TotalBytesOut = 1
    SELECT TOP(@TopCount)
        [Protocol] AS Protocol,
        [Requests] AS Requests,
        (CAST([Requests] AS decimal) / @TotalRequests) AS RequestRatio,
        [BytesIn] AS BytesIn,
        (CAST([BytesIn] AS decimal) / @TotalBytesIn) AS BytesInRatio,
        [BytesOut] AS BytesOut,
        (CAST([BytesOut] AS decimal) / @TotalBytesOut) AS BytesOutRatio,
        [TotalBytes] AS TotalBytes,
        (CAST([TotalBytes] AS decimal) / @totalBytes) AS TotalByteRatio
    FROM #Table1
    WHERE [Protocol] != N'{[23124]}'
    UNION ALL
    SELECT
       N'{[23124]}' As Protocol,
        SUM(Requests),
        (CAST(SUM(Requests) AS decimal) / @TotalRequests) AS RequestRatio,
        SUM([BytesIn]),
        (CAST(SUM([BytesIn]) AS decimal) / @TotalBytesIn) AS BytesInRatio,
        SUM([BytesOut]),
        (CAST(SUM([BytesOut]) AS decimal) / @TotalBytesOut) AS BytesOutRatio,
        SUM([TotalBytes]) AS TotalBytes,
        (CAST(SUM([TotalBytes]) AS decimal) / @totalBytes) AS TotalByteRatio
    FROM #Table1 t1
    WHERE NOT EXISTS
        (SELECT * FROM
           (SELECT TOP(@TopCount) *
            FROM #Table1
            WHERE [Protocol] != N'{[23124]}') t2
         WHERE t1.Protocol = t2.Protocol)
END