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
/
25046
< prev
next >
Wrap
Text File
|
2010-06-15
|
4KB
|
107 lines
CREATE PROCEDURE [dbo].[ISA_spUserSummaryTrim]
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID ('tblUserSummary', 'u') IS NULL
RETURN
DECLARE @FromDate datetime
SET @FromDate = (SELECT [FromDate] FROM [tblSummaryParams])
CREATE TABLE #Table1
(
UserName nvarchar(514),
BytesOut bigint,
BytesIn bigint,
TotalBytes bigint,
Requests bigint,
AuthorizedFailures bigint,
WebBytesOut bigint,
WebBytesIn bigint,
WebTotalBytes bigint,
WebRequests bigint,
[date] datetime
);
DECLARE @SQLString1 nvarchar(2000) =
'SELECT *
FROM (
SELECT
[UserName], [BytesOut], [BytesIn], [TotalBytes], [Requests], [AuthorizedFailures], [WebBytesOut], [WebBytesIn], [WebTotalBytes], [WebRequests],[Date]
FROM (
SELECT
[UserName], [BytesOut], [BytesIn], [TotalBytes], [Requests], [AuthorizedFailures], [WebBytesOut], [WebBytesIn], [WebTotalBytes], [WebRequests], [Date],
ROW_NUMBER() OVER (ORDER BY '
DECLARE @SQLString2 nvarchar(2000) =
' DESC) AS rownum
FROM dbo.tblUserSummary
WHERE '
DECLARE @SQLString3 nvarchar(2000) =
' > 0 ) t
WHERE rownum <= 5000
) ttt
WHERE [UserName] IS NOT NULL
AND NOT EXISTS (SELECT * FROM #Table1 WHERE ttt.[UserName] = [UserName])'
DECLARE @SQLString nvarchar(4000)
DECLARE @OrderString nvarchar(200)
SET @OrderString = '[AuthorizedFailures]'
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString);
SET @OrderString = '[Requests]'
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString);
SET @OrderString = '[TotalBytes]';
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);
SET @OrderString = '[WebRequests]';
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString);
SET @OrderString = '[WebTotalBytes]';
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString);
SET @OrderString = '[WebBytesIn]';
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString);
SET @OrderString = '[WebBytesOut]';
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString);
SET @OrderString = '[Requests] - [WebRequests]';
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString);
SET @OrderString = '[TotalBytes] - [WebTotalBytes]';
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString);
SET @OrderString = '[BytesIn] - [WebBytesIn]';
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString);
SET @OrderString = '[BytesOut] - [WebBytesOut]';
SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
INSERT INTO #Table1
EXECUTE(@SQLString);
---- now add tail
INSERT INTO #TABLE1
SELECT
N'{[23124]}', SUM([BytesOut]), SUM([BytesIn]), SUM([TotalBytes]), SUM([Requests]), SUM([AuthorizedFailures]), SUM([WebBytesOut] ), SUM([WebBytesIn]), SUM([WebTotalBytes]), SUM([WebRequests]), @FromDate
FROM dbo.tblUserSummary
WHERE [UserName] NOT IN (SELECT [UserName] FROM #Table1)
TRUNCATE TABLE [tblUserSummary]
INSERT INTO [tblUserSummary]
SELECT *
FROM #TABLE1
END