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 / 25076 < prev    next >
Text File  |  2010-06-15  |  3KB  |  75 lines
  1. CREATE PROCEDURE [dbo].[ISA_spProtocolSummaryTrim]
  2. AS
  3. BEGIN
  4.     SET NOCOUNT ON;
  5.     IF OBJECT_ID ('tblProtocolSummary', 'u') IS NULL
  6.         RETURN
  7.     DECLARE @FromDate datetime
  8.     SET @FromDate = (SELECT [FromDate] FROM [tblSummaryParams])
  9.     CREATE TABLE #Table1
  10.     (
  11.         [ProtocolName] nvarchar(128),
  12.         [BytesIn] bigint,
  13.         [BytesOut] bigint,
  14.         [TotalBytes] bigint,
  15.         [Requests] bigint,
  16.         [LogType] bit,
  17.         [Date] datetime
  18.     )
  19.     DECLARE @SQLString1 nvarchar(2000) =
  20.     'SELECT *
  21.     FROM (
  22.         SELECT
  23.             [ProtocolName], [BytesIn], [BytesOut], [TotalBytes], [Requests], [LogType], [Date]
  24.         FROM (
  25.             SELECT
  26.                 [ProtocolName], [BytesIn], [BytesOut], [TotalBytes], [Requests], [LogType], [Date],
  27.                 ROW_NUMBER() OVER (PARTITION BY [LogType] ORDER BY '
  28.     DECLARE @SQLString2 nvarchar(2000) =
  29.                     ' DESC) AS rownum
  30.             FROM dbo.[tblProtocolSummary]
  31.             WHERE '
  32.     DECLARE @SQLString3 nvarchar(2000) =
  33.             ' > 0) t
  34.         WHERE rownum <= 5000
  35.     ) ttt
  36.     WHERE [ProtocolName] IS NOT NULL
  37.           AND NOT EXISTS (SELECT * FROM #Table1
  38.                           WHERE ttt.[ProtocolName] = [ProtocolName] AND
  39.                                 ttt.[LogType] = [LogType])'
  40.     DECLARE @SQLString nvarchar(4000)
  41.     DECLARE @OrderString nvarchar(200)
  42.     SET @OrderString = '[TotalBytes]'
  43.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  44.     PRINT @SQLString
  45.     INSERT INTO #Table1
  46.     EXECUTE(@SQLString)
  47.     SET @OrderString = '[Requests]';
  48.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  49.     INSERT INTO #Table1
  50.     EXECUTE(@SQLString)
  51.     SET @OrderString = '[BytesIn]';
  52.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  53.     INSERT INTO #Table1
  54.     EXECUTE(@SQLString)
  55.     SET @OrderString = '[BytesOut]';
  56.     SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
  57.     INSERT INTO #Table1
  58.     EXECUTE(@SQLString)
  59.     ---- now add tail
  60.     INSERT INTO #TABLE1
  61.     SELECT
  62.         N'{[23124]}', SUM([BytesIn]), SUM([BytesOut]), SUM([TotalBytes]), SUM([Requests]), [LogType], @FromDate
  63.     FROM dbo.[tblProtocolSummary] ttt
  64.     WHERE
  65.         NOT EXISTS (SELECT *
  66.                     FROM #Table1
  67.                     WHERE ttt.[ProtocolName] = [ProtocolName] AND
  68.                         ttt.[LogType] = [LogType])
  69.     GROUP BY [LogType]
  70.     TRUNCATE TABLE [tblProtocolSummary]
  71.     INSERT INTO [tblProtocolSummary]
  72.     SELECT *
  73.     FROM #TABLE1
  74. END
  75.