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

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

CREATE PROCEDURE [dbo].[ISA_spUrlfCategoriesRDL]
    @FromDate datetime,
    @ToDate datetime,
    @ReportType varchar(10),
    @TopCount int,
    @SortOrder varchar(100)
AS
BEGIN
    DECLARE @SummTableName varchar(100)
    SET @SummTableName = dbo.fnGetSummaryTableName('tblUrlfSummary', @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
    (
        UrlCategory nvarchar(255),
        Requests bigint,
        BytesIn bigint,
        BytesOut bigint,
        TotalBytes 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,
            SUM(o.BytesIn) AS BytesIn,
            SUM(o.BytesOut) AS BytesOut,
            SUM(o.TotalBytes) AS TotalBytes
        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.[UrlCategoryId] >= 0)
            AND  o.[Action] = 0
        GROUP BY oo.UrlCategoryName
        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)
        UrlCategory,
        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
    UNION ALL
    SELECT
        N'{[23124]}' AS UrlCategory,
        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) t2
            WHERE t1.UrlCategory = t2.UrlCategory)
END