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
/
25450
< prev
next >
Wrap
Text File
|
2010-06-15
|
2KB
|
65 lines
CREATE PROCEDURE [dbo].[ISA_spTopProtocolsRDL]
@FromDate datetime,
@ToDate datetime,
@ReportType varchar(10),
@TopCount int
AS
BEGIN
DECLARE @SummTableName varchar(100)
SET @SummTableName = dbo.fnGetSummaryTableName('tblProtocolSummary', @FromDate, @ToDate, @ReportType)
CREATE TABLE #Table1
(
Protocol nvarchar(128),
Requests bigint
);
IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
BEGIN
DECLARE @strQuery varchar(8000)
SET @strQuery =
'SELECT
[Protocol] AS Protocol,
SUM([Requests]) AS Requests
FROM ' + @SummTableName + '
WHERE
([date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
GROUP BY [Protocol]
'
INSERT #Table1
EXEC(@strQuery)
END
DECLARE @AllRequests bigint
SET @AllRequests = (SELECT SUM(Requests) FROM #Table1);
IF (@AllRequests =0)
SET @AllRequests = 1
CREATE TABLE #Table2
(
Protocol nvarchar(128),
Requests bigint
);
INSERT INTO #Table2
SELECT
Protocol,
SUM(Requests) AS Requests
FROM #Table1
GROUP BY Protocol
ORDER BY Requests DESC;
SELECT TOP(@TopCount)
Protocol,
Requests,
(CAST(Requests AS decimal) / @AllRequests) AS RequestRatio
FROM #Table2
WHERE [Protocol] != N'{[23124]}'
UNION ALL
SELECT
N'{[23124]}' As Protocol,
SUM(Requests),
(CAST(SUM(Requests) AS decimal) / @AllRequests) AS RequestRatio
FROM #Table2 t1
WHERE NOT EXISTS
(SELECT * FROM
(SELECT TOP(@TopCount) *
FROM #Table2
WHERE [Protocol] != N'{[23124]}') t2
WHERE t1.Protocol = t2.Protocol)
END