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
/
25073
< prev
next >
Wrap
Text File
|
2010-06-15
|
3KB
|
87 lines
CREATE PROCEDURE [dbo].[ISA_spProtocolSummaryMonthly]
@CurrentMonthFirstDay datetime,
@CurrentMonthLastDay datetime,
@LastDailyToKeep datetime,
@LastMonthlyToKeep datetime
AS
BEGIN
SET NOCOUNT ON;
-- move dailies to monthlies
SELECT [Protocol] AS [Protocol],
SUM([BytesIn]) AS [BytesIn],
SUM([BytesOut]) AS [BytesOut],
SUM([TotalBytes]) AS [TotalBytes],
SUM([Requests]) AS [Requests],
[LogType] AS [LogType],
@CurrentMonthFirstDay AS [Date]
INTO #TableOrig
FROM dbo.tblProtocolSummary_Daily
WHERE (Date >= @CurrentMonthFirstDay) AND
(Date <= @CurrentMonthLastDay)
GROUP BY
[Protocol],
[LogType]
-- create #table1 empty table copy
SELECT TOP(0) * INTO #Table1 FROM #TableOrig
DECLARE @SQLString1 nvarchar(2000) =
'SELECT *
FROM (
SELECT
[Protocol], [BytesIn], [BytesOut], [TotalBytes], [Requests], [LogType], [Date]
FROM (
SELECT
[Protocol], [BytesIn], [BytesOut], [TotalBytes], [Requests], [LogType], [Date],
ROW_NUMBER() OVER (PARTITION BY [LogType] ORDER BY '
DECLARE @SQLString2 nvarchar(2000) =
' DESC) AS rownum
FROM #TableOrig
WHERE [Protocol] != N''{[23124]}'' AND '
DECLARE @SQLString3 nvarchar(2000) =
' > 0) t
WHERE rownum <= 5000
) ttt
WHERE [Protocol] IS NOT NULL
AND NOT EXISTS (SELECT * FROM #Table1
WHERE ttt.[Protocol] = [Protocol] AND
ttt.[LogType] = [LogType] )'
DECLARE @SQLString nvarchar(4000)
DECLARE @OrderString nvarchar(200)
SET @OrderString = '[TotalBytes]'
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
PRINT @SQLString
INSERT INTO #Table1
EXECUTE(@SQLString)
SET @OrderString = '[Requests]';
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString)
SET @OrderString = '[BytesIn]';
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString)
SET @OrderString = '[BytesOut]';
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString)
---- now add tail
INSERT INTO #TABLE1
SELECT
N'{[23124]}', SUM([BytesIn]), SUM([BytesOut]), SUM([TotalBytes]), SUM([Requests]), [LogType], @CurrentMonthFirstDay
FROM #TableOrig ttt
WHERE
NOT EXISTS (SELECT *
FROM #Table1
WHERE ttt.[Protocol] = [Protocol] AND
ttt.[LogType] = [LogType])
GROUP BY [LogType]
INSERT INTO [tblProtocolSummary_Monthly]
SELECT *
FROM #TABLE1
-- cleanup dailies
DELETE FROM [ISA_RS_Db].[dbo].[tblProtocolSummary_Daily]
WHERE (Date < @LastDailyToKeep)
-- cleanup monthlies
DELETE FROM [ISA_RS_Db].[dbo].[tblProtocolSummary_Monthly]
WHERE (Date < @LastMonthlyToKeep)
END