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

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

CREATE PROCEDURE [dbo].[ISA_spTrimArrayDroppedUserSummary]
    @FromDate datetime,
    @ToDate   datetime
AS
BEGIN
    SET NOCOUNT ON;
    IF OBJECT_ID ('tblDroppedUserSummary_Daily', 'u') IS NULL
        RETURN
    -- create #table1 empty table copy
    SELECT TOP(0) * INTO #Table1 FROM [tblDroppedUserSummary_Daily]
    DELETE FROM [tblDroppedUserSummary_Daily]
    OUTPUT DELETED.* INTO #Table1
    WHERE ([Date] >= @FromDate) AND ([Date] < @ToDate)
    SELECT
        [UserName] AS UserName,
        SUM([DroppedPackets]) AS DroppedPackets,
        [Date] AS Date
    INTO #TableOrig
    FROM #Table1
    GROUP BY
        [UserName],
        [Date]
    TRUNCATE TABLE #Table1
    INSERT INTO #TABLE1
    SELECT *
    FROM (
        SELECT
            [UserName], [DroppedPackets], [Date]
        FROM (
            SELECT
                [UserName], [DroppedPackets], [Date],
                ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY [DroppedPackets] DESC) AS rownum
                FROM #TableOrig
                WHERE [UserName] != N'{[23124]}'
            ) t
        WHERE rownum <= 10000
        ) ttt
    ---- now add tail
    INSERT INTO #TABLE1
    SELECT
        N'{[23124]}', SUM([DroppedPackets]), [Date]
    FROM #TableOrig ttt
    WHERE
        [UserName] IS NOT NULL AND
        NOT EXISTS (SELECT *
                    FROM #Table1
                    WHERE ttt.[UserName] = [UserName] AND
                        ttt.[Date] = [Date])
    GROUP BY [Date]
    INSERT INTO [tblDroppedUserSummary_Daily]
    SELECT *
    FROM #TABLE1
END