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 / 25053 < prev    next >
Text File  |  2010-06-15  |  1KB  |  36 lines
  1. CREATE PROCEDURE [dbo].[ISA_spUsageSummaryMonthly] 
  2.     @CurrentMonthFirstDay datetime,
  3.     @CurrentMonthLastDay datetime,
  4.     @LastDailyToKeep datetime,
  5.     @LastMonthlyToKeep datetime
  6. AS
  7. BEGIN
  8.     SET NOCOUNT ON;
  9.  
  10. -- move dailies to monthlies (scale up from HOUR intervals to DAY intervals)
  11.     INSERT INTO dbo.tblUsageSummary_Monthly
  12.     SELECT 
  13.          DATEADD(day, DATEPART(day, [Date]) - 1, @CurrentMonthFirstDay) AS Interval,
  14.          SUM([CacheHits]) AS [CacheHits],
  15.          SUM([processingtime]) AS ProcessingTime,
  16.          SUM([TotalBytes]) AS TotalBytes,
  17.          SUM([Requests]) AS Requests,
  18.          SUM([WebRequests]) AS WebRequests,
  19.          MAX([MaxConcurrentConnectionsPerMinute]) AS MaxConcurrentConnectionsPerMinute,
  20.          @CurrentMonthFirstDay AS Date
  21.     FROM dbo.tblUsageSummary_Daily
  22.     WHERE (Date >= @CurrentMonthFirstDay) AND
  23.           (Date <= @CurrentMonthLastDay)
  24.     GROUP BY
  25.          DATEPART(day, [Date])
  26.  
  27. -- cleanup dailies
  28.     DELETE FROM [ISA_RS_Db].[dbo].[tblUsageSummary_Daily]
  29.     WHERE (Date < @LastDailyToKeep)
  30.  
  31. -- cleanup monthlies
  32.     DELETE FROM [ISA_RS_Db].[dbo].[tblUsageSummary_Monthly]
  33.     WHERE (Date < @LastMonthlyToKeep)
  34.  
  35. END
  36.