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

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

CREATE PROCEDURE [dbo].[ISA_spDroppedUserSummaryMonthly]
    @CurrentMonthFirstDay datetime,
    @CurrentMonthLastDay datetime,
    @LastDailyToKeep datetime,
    @LastMonthlyToKeep datetime
AS
BEGIN
    SET NOCOUNT ON;
-- move dailies to monthlies
    SELECT [UserName] AS [UserName]
          ,SUM([DroppedPackets]) AS [DroppedPackets]
          ,@CurrentMonthFirstDay AS [Date]
    INTO #TableOrig
    FROM dbo.tblDroppedUserSummary_Daily
    WHERE (Date >= @CurrentMonthFirstDay) AND
          (Date <= @CurrentMonthLastDay)
    GROUP BY [UserName]
    -- create #table1 empty table copy
    SELECT TOP(0) * INTO #Table1 FROM #TableOrig
    INSERT INTO #TABLE1
    SELECT *
    FROM (
        SELECT
            [UserName], [DroppedPackets], [Date]
        FROM (
            SELECT
                [UserName], [DroppedPackets], [Date],
                ROW_NUMBER() OVER (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]), @CurrentMonthFirstDay
    FROM #TableOrig ttt
    WHERE
        [UserName] IS NOT NULL AND
        NOT EXISTS (SELECT *
                    FROM #Table1
                    WHERE ttt.[UserName] = [UserName])
    INSERT INTO dbo.tblDroppedUserSummary_Monthly
    SELECT *
    FROM #TABLE1
-- cleanup dailies
    DELETE FROM [ISA_RS_Db].[dbo].[tblDroppedUserSummary_Daily]
    WHERE (Date < @LastDailyToKeep)
-- cleanup monthlies
    DELETE FROM [ISA_RS_Db].[dbo].[tblDroppedUserSummary_Monthly]
    WHERE (Date < @LastMonthlyToKeep)
END