home *** CD-ROM | disk | FTP | other *** search
/ 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 / 25108 < prev    next >
Text File  |  2010-06-15  |  3KB  |  81 lines
  1. CREATE PROCEDURE [dbo].[ISA_spDestinationSummaryMonthly]
  2.     @CurrentMonthFirstDay datetime,
  3.     @CurrentMonthLastDay datetime,
  4.     @LastDailyToKeep datetime,
  5.     @LastMonthlyToKeep datetime
  6. AS
  7. BEGIN
  8.     SET NOCOUNT ON;
  9. -- move dailies to monthlies
  10.     SELECT [DestinationIP] AS [DestinationIP],
  11.             SUM([BytesIn]) AS [BytesIn],
  12.             SUM([BytesOut]) AS [BytesOut],
  13.             SUM([TotalBytes]) AS [TotalBytes],
  14.             SUM([Requests]) AS [Requests],
  15.             @CurrentMonthFirstDay AS [Date]
  16.     INTO #TableOrig
  17.     FROM dbo.tblDestinationSummary_Daily
  18.     WHERE (Date >= @CurrentMonthFirstDay) AND
  19.           (Date <= @CurrentMonthLastDay)
  20.     GROUP BY DestinationIP
  21.     -- create #table1 empty table copy
  22.     SELECT TOP(0) * INTO #Table1 FROM #TableOrig
  23.     -- now calculate the new TOPs
  24.     DECLARE @SQLString1 nvarchar(2000) =
  25.     'SELECT *
  26.     FROM (
  27.         SELECT
  28.             [DestinationIP], [BytesIn], [BytesOut], [TotalBytes], [Requests], [Date]
  29.         FROM (
  30.             SELECT
  31.                 [DestinationIP], [BytesIn], [BytesOut], [TotalBytes], [Requests], [Date],
  32.                 ROW_NUMBER() OVER (ORDER BY '
  33.     DECLARE @SQLString2 nvarchar(2000) =
  34.                     ' DESC) AS rownum
  35.             FROM #TableOrig
  36.             WHERE [DestinationIP] != ''00000000-0000-0000-0000-000000000000'' AND '
  37.     DECLARE @SQLString3 nvarchar(2000) =
  38.             ' > 0 ) t
  39.         WHERE rownum <= 5000
  40.     ) ttt
  41.     WHERE [DestinationIP] IS NOT NULL
  42.           AND NOT EXISTS (SELECT * FROM #Table1
  43.                           WHERE ttt.[DestinationIP] = [DestinationIP])'
  44.     DECLARE @SQLString nvarchar(4000)
  45.     DECLARE @OrderString nvarchar(200)
  46.     SET @OrderString = '[TotalBytes]'
  47.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  48.     PRINT @SQLString
  49.     INSERT INTO #Table1
  50.     EXECUTE(@SQLString)
  51.     SET @OrderString = '[Requests]';
  52.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  53.     INSERT INTO #Table1
  54.     EXECUTE(@SQLString)
  55.     SET @OrderString = '[BytesIn]';
  56.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  57.     INSERT INTO #Table1
  58.     EXECUTE(@SQLString)
  59.     SET @OrderString = '[BytesOut]';
  60.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  61.     INSERT INTO #Table1
  62.     EXECUTE(@SQLString)
  63.     ---- now add tail
  64.     INSERT INTO #TABLE1
  65.     SELECT
  66.         '00000000-0000-0000-0000-000000000000', SUM([BytesIn]), SUM([BytesOut]), SUM([TotalBytes]), SUM([Requests]), @CurrentMonthFirstDay
  67.     FROM #TableOrig ttt
  68.     WHERE
  69.         NOT EXISTS (SELECT *
  70.                     FROM #Table1
  71.                     WHERE ttt.[DestinationIP] = [DestinationIP])
  72.     INSERT INTO dbo.tblDestinationSummary_Monthly
  73.     SELECT * FROM #Table1
  74. -- cleanup dailies
  75.     DELETE FROM [ISA_RS_Db].[dbo].[tblDestinationSummary_Daily]
  76.     WHERE (Date < @LastDailyToKeep)
  77. -- cleanup monthlies
  78.     DELETE FROM [ISA_RS_Db].[dbo].[tblDestinationSummary_Monthly]
  79.     WHERE (Date < @LastMonthlyToKeep)
  80. END
  81.