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

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

CREATE PROCEDURE [dbo].[ISA_spDroppedUsersRDL]
    @FromDate datetime,
    @ToDate datetime,
    @ReportType varchar(10),
    @TopCount int
AS
BEGIN
    DECLARE @SummTableName varchar(100)
    SET @SummTableName = dbo.fnGetSummaryTableName('tblDroppedUserSummary', @FromDate, @ToDate, @ReportType)
    CREATE TABLE #Table1
    (
        UserName nvarchar(514),
        DroppedPackets bigint
    )
    IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
    BEGIN
        DECLARE @strQuery varchar(8000)
        SET @strQuery =
        'SELECT
            UserName,
            SUM([DroppedPackets])
        FROM ' + @SummTableName + '
        WHERE ([date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
            AND ([DroppedPackets] <> 0)
        GROUP BY [UserName]
        ORDER BY SUM([DroppedPackets]) DESC'
        INSERT #Table1
        EXEC(@strQuery)
    END
    DECLARE @TotalDrops bigint
    SET @TotalDrops = (SELECT SUM([DroppedPackets]) FROM #Table1)
    CREATE TABLE #Table2
    (
        UserName nvarchar(514),
        DroppedPackets bigint
    )
    INSERT INTO #Table2
    SELECT TOP(@TopCount)
        [UserName],
        [DroppedPackets]
    FROM #Table1
    WHERE [UserName] != N'{[23124]}'
    INSERT INTO #Table2
    SELECT
       N'{[23124]}' As UserName,
        SUM(DroppedPackets)
    FROM #Table1 t1
    WHERE NOT EXISTS
        (SELECT * FROM #Table2 t2
            WHERE t1.UserName = t2.UserName)
    SELECT
        [dbo].[fnUserOrIpAddressToText]([UserName]) AS [UserName],
        [DroppedPackets] AS [DroppedPackets],
        CAST([DroppedPackets] AS DECIMAL)/@TotalDrops AS DropRatio
    FROM #Table2
END