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

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

CREATE PROCEDURE [dbo].[ISA_spCacheSummaryRDL]
    @FromDate datetime,
    @ToDate datetime,
    @ReportType varchar(10),
    @SortOrder varchar(100)
AS
BEGIN
 -- the Cache table contain values that we want to map in the following way:
 -- Cache + NotModified + NVCache = Cache without verification
 -- VCache - Cache with verification
 -- Inet + Upstream = Internet
 -- VFInet -  Internet with cache update
    DECLARE @SummTableName varchar(100)
    SET @SummTableName = dbo.fnGetSummaryTableName('tblCacheSummary', @FromDate, @ToDate, @ReportType)
    CREATE TABLE #Table1
    (
        ResponseInt int,
        Requests bigint,
        TotalBytes bigint
    )
    INSERT INTO #Table1 ([ResponseInt],[Requests],[TotalBytes])
    VALUES (0,0,0)
    INSERT INTO #Table1 ([ResponseInt],[Requests],[TotalBytes])
    VALUES (1,0,0)
    INSERT INTO #Table1 ([ResponseInt],[Requests],[TotalBytes])
    VALUES (2,0,0)
    INSERT INTO #Table1 ([ResponseInt],[Requests],[TotalBytes])
    VALUES (4,0,0)
    INSERT INTO #Table1 ([ResponseInt],[Requests],[TotalBytes])
    VALUES (5,0,0)
    IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
    BEGIN
        DECLARE @strQuery varchar(8000)
        SET @strQuery =
        'SELECT
            ResponseInt =
            CASE
                WHEN [Response] IN (1, 3, 8) THEN 1
                WHEN [Response] = 2 THEN 2
                WHEN [Response] IN (5, 7) THEN 5
                WHEN [Response] = 4 THEN 4
                ELSE 0
            END,
            Requests,
            TotalBytes
        FROM ' + @SummTableName + '
        WHERE ([date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
        '
        INSERT #Table1
        EXEC(@strQuery)
    END
    DECLARE @TotalRequests bigint
    SET @TotalRequests = (SELECT SUM([Requests]) FROM #Table1)
    IF (@TotalRequests = 0)
        SET @TotalRequests = 1
    SELECT
        CASE
            WHEN [ResponseInt] = 1 THEN N'{[23026]}'
            WHEN [ResponseInt] = 2 THEN N'{[23027]}'
            WHEN [ResponseInt] = 5 THEN N'{[23029]}'
            WHEN [ResponseInt] = 4 THEN N'{[23030]}'
            ELSE N'{[23031]}'
        END AS Response,
        SUM([Requests]) AS Requests,
        CAST(SUM([Requests]) AS DECIMAL) / @TotalRequests AS RequestRatio,
        SUM([TotalBytes]) AS TotalBytes
    INTO #Table2
    FROM #Table1
    GROUP BY
        [ResponseInt]
    SELECT *
    FROM #Table2
    ORDER BY
        CASE
            WHEN @SortOrder = 'Requests' THEN [Requests]
            ELSE [TotalBytes]
        END DESC;
END