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
/
25438
< prev
next >
Wrap
Text File
|
2010-06-15
|
2KB
|
57 lines
CREATE PROCEDURE [dbo].[ISA_spDroppedUsersRDL]
@FromDate datetime,
@ToDate datetime,
@ReportType varchar(10),
@TopCount int
AS
BEGIN
DECLARE @SummTableName varchar(100)
SET @SummTableName = dbo.fnGetSummaryTableName('tblDroppedUserSummary', @FromDate, @ToDate, @ReportType)
CREATE TABLE #Table1
(
UserName nvarchar(514),
DroppedPackets bigint
)
IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
BEGIN
DECLARE @strQuery varchar(8000)
SET @strQuery =
'SELECT
UserName,
SUM([DroppedPackets])
FROM ' + @SummTableName + '
WHERE ([date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
AND ([DroppedPackets] <> 0)
GROUP BY [UserName]
ORDER BY SUM([DroppedPackets]) DESC'
INSERT #Table1
EXEC(@strQuery)
END
DECLARE @TotalDrops bigint
SET @TotalDrops = (SELECT SUM([DroppedPackets]) FROM #Table1)
CREATE TABLE #Table2
(
UserName nvarchar(514),
DroppedPackets bigint
)
INSERT INTO #Table2
SELECT TOP(@TopCount)
[UserName],
[DroppedPackets]
FROM #Table1
WHERE [UserName] != N'{[23124]}'
INSERT INTO #Table2
SELECT
N'{[23124]}' As UserName,
SUM(DroppedPackets)
FROM #Table1 t1
WHERE NOT EXISTS
(SELECT * FROM #Table2 t2
WHERE t1.UserName = t2.UserName)
SELECT
[dbo].[fnUserOrIpAddressToText]([UserName]) AS [UserName],
[DroppedPackets] AS [DroppedPackets],
CAST([DroppedPackets] AS DECIMAL)/@TotalDrops AS DropRatio
FROM #Table2
END