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 / 25446 < prev    next >
Text File  |  2010-06-15  |  3KB  |  76 lines
  1. CREATE PROCEDURE [dbo].[ISA_spDailyTrafficSummaryRDL]
  2.     @ReportType varchar(10),
  3.     @FromDate datetime, 
  4.     @ToDate datetime
  5. AS
  6. BEGIN
  7.  
  8.     DECLARE @SummTableName varchar(100)
  9.     SET @SummTableName = dbo.fnGetSummaryTableName('tblTrafficSummary', @FromDate, @ToDate, @ReportType)
  10.  
  11.     CREATE TABLE #Table1
  12.     (
  13.             Interval int,
  14.             RequestsPerSecond float,
  15.             TotalBytesPerSecond bigint,
  16.             TotalCachedProcessingTime bigint,
  17.             TotalCachedRequests bigint,
  18.             TotalNonCachedProcessingTime bigint,
  19.             TotalNonCachedRequests bigint,
  20.             MaxConcurrentConnectionsPerMinute bigint,
  21.             TotalCacheHits bigint,
  22.             TotalWebRequests bigint,
  23.             TotalBytes bigint
  24.     )
  25.     
  26.     IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
  27.     BEGIN
  28.         DECLARE @strQuery varchar(8000)
  29.         SET @strQuery = 
  30.         'SELECT
  31.             [Interval] AS Interval,
  32.             CAST (SUM([Requests]) As Decimal)/(15*60) As RequestsPerSecond,
  33.             SUM([TotalBytes])/(15*60) AS TotalBytesPerSecond,
  34.             SUM([CachedProcessingTime]) AS TotalCachedProcessingTime,
  35.             SUM([CachedRequests]) AS TotalCachedRequests,
  36.             SUM([NonCachedProcessingTime]) AS TotalNonCachedProcessingTime,
  37.             SUM([NonCachedRequests]) AS TotalNonCachedRequests,
  38.             MAX([MaxConcurrentConnectionsPerMinute]) AS MaxConcurrentConnectionsPerMinute,
  39.             SUM([CacheHits]) AS TotalCacheHits,
  40.             SUM([WebRequests]) AS TotalWebRequests,
  41.             SUM([TotalBytes]) AS TotalBytes
  42.         FROM ' + @SummTableName + ' 
  43.         WHERE ([date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
  44.         GROUP BY 
  45.             [Interval]
  46.         '
  47.         INSERT #Table1
  48.         EXEC(@strQuery)
  49.  
  50.     END
  51.  
  52.     DECLARE @ZeroTime smalldatetime = '1999-1-1'
  53.     
  54.     SELECT 
  55.          DATEADD(minute, [Interval] , @ZeroTime) AS Interval,
  56.          [RequestsPerSecond],
  57.          [TotalBytesPerSecond],
  58.          CASE
  59.              WHEN [TotalCachedRequests] = 0 THEN 0
  60.              ELSE [TotalCachedProcessingTime]/[TotalCachedRequests]/1000 
  61.          END AS CachedProcessingTime,
  62.          CASE
  63.              WHEN [TotalNonCachedRequests] = 0 THEN 0
  64.              ELSE [TotalNonCachedProcessingTime]/[TotalNonCachedRequests]/1000 
  65.          END AS NonCachedProcessingTime,
  66.          [MaxConcurrentConnectionsPerMinute],
  67.          CASE
  68.              WHEN [TotalWebRequests] = 0 THEN 0
  69.              ELSE CAST([TotalCacheHits] AS decimal)/[TotalWebRequests] 
  70.          END AS CacheHitRatio,
  71.          [TotalBytes]
  72.     FROM #Table1
  73.     ORDER BY [Interval] ASC
  74.  
  75. END
  76.