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 / 25193 < prev    next >
Text File  |  2010-06-15  |  5KB  |  127 lines
  1. CREATE PROCEDURE [dbo].[ISA_spTrimArraySiteSummary]
  2.     @FromDate datetime,
  3.     @ToDate   datetime
  4. AS
  5. BEGIN
  6.     SET NOCOUNT ON;
  7.     IF OBJECT_ID ('tblSiteSummary_Daily', 'u') IS NULL
  8.         RETURN
  9.     -- create #table1 empty table copy
  10.     SELECT TOP(0) * INTO #Table1 FROM [tblSiteSummary_Daily]
  11.     DELETE FROM [tblSiteSummary_Daily]
  12.     OUTPUT DELETED.* INTO #Table1
  13.     WHERE ([Date] >= @FromDate) AND ([Date] < @ToDate)
  14.     SELECT [Site],
  15.         SUM([BytesIn]) AS BytesIn,
  16.         SUM([BytesOut]) AS BytesOut,
  17.         SUM([TotalBytes]) AS TotalBytes,
  18.         SUM([Requests]) AS Requests,
  19.         [UrlCategoryId] AS [UrlCategoryId],
  20.         SUM([MalwareIncidents]) AS MalwareIncidents,
  21.         SUM([MalwareInspectionDuration]) AS [MalwareInspectionDuration],
  22.         [Action],
  23.         [Date] AS Date
  24.     INTO #TableOrig
  25.     FROM #Table1
  26.     GROUP BY
  27.         [Date],
  28.         [Site],
  29.         [UrlCategoryId],
  30.         [Action],
  31.         CASE
  32.             WHEN [MalwareInspectionDuration] = 0 THEN 0
  33.             ELSE 1
  34.         END
  35.     TRUNCATE TABLE #Table1
  36.     INSERT INTO #Table1
  37.     SELECT * FROM #TableOrig WHERE [MalwareIncidents] > 0
  38.     DECLARE @SQLString1 nvarchar(2000) =
  39.     'SELECT *
  40.     FROM (
  41.         SELECT
  42.             [Site], [BytesIn], [BytesOut], [TotalBytes], [Requests], [URLCategoryId], [MalwareIncidents], [MalwareInspectionDuration], [Action], [Date]
  43.         FROM (
  44.             SELECT
  45.                 [Site], [BytesIn], [BytesOut], [TotalBytes], [Requests], [URLCategoryId], [MalwareIncidents], [MalwareInspectionDuration], [Action], [Date],
  46.                 ROW_NUMBER() OVER (PARTITION BY [Date], [Action] ORDER BY '
  47.     DECLARE @SQLString2 nvarchar(2000) =
  48.                     ' DESC) AS rownum
  49.             FROM #TableOrig
  50.             WHERE [Site] != N''{[23124]}'' AND '
  51.     DECLARE @SQLString3 nvarchar(2000) =
  52.             ' > 0 ) t
  53.         WHERE rownum <= 5000
  54.     ) ttt
  55.     WHERE [Site] IS NOT NULL
  56.           AND [MalwareIncidents] = 0
  57.           AND NOT EXISTS (SELECT * FROM #Table1
  58.                           WHERE ttt.[Site] = [Site] AND
  59.                                 ttt.[BytesIn] = [BytesIn] AND
  60.                                 ttt.[BytesOut] = [BytesOut] AND
  61.                                 ttt.[TotalBytes] = [TotalBytes] AND
  62.                                 ttt.[Requests] = [Requests] AND
  63.                                 ttt.[URLCategoryId] = [URLCategoryId] AND
  64.                                 ttt.[MalwareIncidents] = [MalwareIncidents] AND
  65.                                 ttt.[MalwareInspectionDuration] = [MalwareInspectionDuration] AND
  66.                                 ttt.[Action] = [Action] AND
  67.                                 ttt.[Date] = [Date]
  68.                                 )'
  69.     DECLARE @SQLString nvarchar(4000)
  70.     DECLARE @OrderString nvarchar(200)
  71.     SET @OrderString = '[Requests]'
  72.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  73.     INSERT INTO #Table1
  74.     EXECUTE(@SQLString)
  75.     SET @OrderString = '[TotalBytes]';
  76.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  77.     INSERT INTO #Table1
  78.     EXECUTE(@SQLString)
  79.     SET @OrderString = '[BytesIn]';
  80.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  81.     INSERT INTO #Table1
  82.     EXECUTE(@SQLString)
  83.     SET @OrderString = '[BytesOut]';
  84.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  85.     INSERT INTO #Table1
  86.     EXECUTE(@SQLString)
  87.     ---- now add tail
  88.     INSERT INTO #TABLE1
  89.     SELECT
  90.         N'{[23124]}' AS [Site],
  91.         SUM([BytesIn]),
  92.         SUM([BytesOut]),
  93.         SUM([TotalBytes]),
  94.         SUM([Requests]),
  95.         ' ' AS [URLCategoryId],
  96.         0 AS [MalwareIncidents],
  97.         SUM([MalwareInspectionDuration]),
  98.         [Action],
  99.         [Date]
  100.     FROM #TableOrig ttt
  101.     WHERE
  102.         [Requests] IS NOT NULL AND
  103.         [MalwareIncidents] = 0 AND
  104.         NOT EXISTS (SELECT *
  105.                     FROM #Table1
  106.                     WHERE ttt.[Site] = [Site] AND
  107.                         ttt.[BytesIn] = [BytesIn] AND
  108.                         ttt.[BytesOut] = [BytesOut] AND
  109.                         ttt.[TotalBytes] = [TotalBytes] AND
  110.                         ttt.[Requests] = [Requests] AND
  111.                         ttt.[URLCategoryId] = [URLCategoryId] AND
  112.                         ttt.[MalwareIncidents] = [MalwareIncidents] AND
  113.                         ttt.[MalwareInspectionDuration] = [MalwareInspectionDuration] AND
  114.                         ttt.[Action] = [Action] AND
  115.                         ttt.[Date] = [Date] )
  116.     GROUP BY
  117.         [Date],
  118.         [Action],
  119.         CASE
  120.             WHEN [MalwareInspectionDuration] = 0 THEN 0
  121.             ELSE 1
  122.         END
  123.     INSERT INTO [tblSiteSummary_Daily]
  124.     SELECT *
  125.     FROM #TABLE1
  126. END
  127.