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

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

CREATE PROCEDURE [dbo].[ISA_spSoftBlockSitesRDL]
    @FromDate datetime,
    @ToDate datetime,
    @ReportType varchar(10),
    @TopCount int
AS
BEGIN
    DECLARE @SummTableName varchar(100)
    SET @SummTableName = dbo.fnGetSummaryTableName('tblSoftBlockSummary', @FromDate, @ToDate, @ReportType)
    CREATE TABLE #Table1
    (
        Site varchar(2048),
        UrlCategoryName nvarchar(255),
        Users bigint,
        Requests bigint
    )
    IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
    BEGIN
        CREATE TABLE #CompactUsers
        (
            Site varchar(2048),
            UrlCategoryName nvarchar(255),
            UserName nvarchar(514),
            Requests bigint
        )
        DECLARE @strQuery varchar(8000)
        SET @strQuery =
        'SELECT DISTINCT
            summary.[Site] AS [Site],
            urlMeta.[URLCategoryName] AS [URLCategoryName],
            summary.[UserName] AS [UserName],
            SUM(summary.[Requests]) AS [Requests]
        FROM ' + @SummTableName + ' AS summary INNER JOIN [tblUrlfMetadata] AS urlMeta ON summary.[URLCategoryId] = urlMeta.[URLCategoryId]
        WHERE (summary.[date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND summary.[date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
        GROUP BY [Site], [URLCategoryName], [UserName]
        '
        INSERT INTO #CompactUsers
        EXEC(@strQuery)
        INSERT INTO #Table1
        SELECT DISTINCT
            [Site],
            [URLCategoryName],
            COUNT(*) AS [Users],
            SUM([Requests]) AS [Requests]
        FROM #CompactUsers
        GROUP BY [Site], [URLCategoryName]
        ORDER BY [Requests] DESC
    END
    DECLARE @TotalRequests bigint
    SET @TotalRequests = (SELECT SUM([Requests]) FROM #Table1)
    IF (@TotalRequests = 0)
        SET @TotalRequests = 1
    SELECT TOP(@TopCount)
        Site AS Site,
        URLCategoryName AS UrlCategoryName,
        Users AS Users,
        Requests AS Requests,
        (CAST([Requests] AS decimal) / @TotalRequests) AS RequestRatio
    FROM #Table1
    WHERE [Site] != N'{[23124]}'
    UNION ALL
    SELECT
        N'{[23124]}' AS Site,
        N' ' AS UrlCategoryName,
        SUM([Users]) AS Users,
        SUM([Requests]) AS Requests,
        (CAST(SUM([Requests]) AS decimal) / @TotalRequests) AS RequestRatio
    FROM #Table1 unprocessedData
    WHERE NOT EXISTS
    (
        SELECT *
        FROM
        (
            SELECT TOP(@TopCount) *
            FROM #Table1
            WHERE [Site] != N'{[23124]}'
        ) processedData
        WHERE
        (
            (unprocessedData.Site = processedData.Site) AND
            (unprocessedData.URLCategoryName = processedData.URLCategoryName)
        )
    )
END