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

Text File | 2010-06-15 | 2KB | 56 lines

CREATE PROCEDURE [dbo].[ISA_spUrlfBlockedCategoriesRDL]
    @FromDate datetime,
    @ToDate datetime,
    @ReportType varchar(10),
    @TopCount int
AS
BEGIN
    DECLARE @SummTableName varchar(100)
    SET @SummTableName = dbo.fnGetSummaryTableName('tblUrlfSummary', @FromDate, @ToDate, @ReportType)
    CREATE TABLE #Table1
    (
        UrlCategory nvarchar(255),
        Requests bigint
    )
    IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
    BEGIN
        DECLARE @strQuery varchar(8000)
        SET @strQuery =
        'SELECT
            oo.UrlCategoryName AS UrlCategory,
            SUM(o.Requests) AS Requests
        FROM ' + @SummTableName + ' AS o
        INNER JOIN [tblUrlfMetadata] AS oo
           ON o.[UrlCategoryId] = oo.[UrlCategoryId]
        WHERE (o.[date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND o.[date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
            AND o.[Action] = 1
            AND o.[UrlfMatched] = 1
            AND (o.[UrlCategoryId] >= 0)
        GROUP BY oo.UrlCategoryName
        ORDER BY Requests DESC;
        '
        INSERT #Table1
        EXEC(@strQuery)
    END
    DECLARE @TotalRequests bigint
    SET @TotalRequests = (SELECT SUM(Requests) FROM #Table1)
    IF (@TotalRequests = 0)
        SET @TotalRequests = 1
    SELECT TOP(@TopCount)
        UrlCategory,
        Requests,
        (CAST(Requests AS decimal) / @TotalRequests) AS RequestRatio
    FROM #Table1
    UNION ALL
    SELECT
        N'{[23124]}' AS UrlCategory,
        SUM(Requests),
        (CAST(SUM(Requests) AS decimal) / @TotalRequests) AS RequestRatio
    FROM #Table1 t1
    WHERE NOT EXISTS
        (SELECT * FROM
           (SELECT TOP(@TopCount) *
            FROM #Table1) t2
            WHERE t1.UrlCategory = t2.UrlCategory)
END