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
/
25458
< prev
next >
Wrap
Text File
|
2010-06-15
|
3KB
|
94 lines
CREATE PROCEDURE [dbo].[ISA_spTopWebsitesRDL]
@FromDate datetime,
@ToDate datetime,
@ReportType varchar(10),
@TopCount int,
@SortOrder varchar(100)
AS
BEGIN
DECLARE @SummTableName varchar(100)
SET @SummTableName = dbo.fnGetSummaryTableName('tblSiteSummary', @FromDate, @ToDate, @ReportType)
DECLARE @OrderColumn varchar(100)
SET @OrderColumn =
CASE
WHEN @SortOrder = 'Requests' THEN 'Requests'
WHEN @SortOrder = 'BytesIn' THEN 'BytesIn'
WHEN @SortOrder = 'BytesOut' THEN 'BytesOut'
ELSE 'TotalBytes'
END;
CREATE TABLE #Table1
(
Site varchar(2048),
Requests bigint,
BytesIn bigint,
BytesOut bigint,
TotalBytes bigint,
)
IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
BEGIN
DECLARE @strQuery varchar(8000)
SET @strQuery =
'SELECT
Site,
SUM(Requests) AS Requests,
SUM(BytesIn) AS BytesIn,
SUM(BytesOut) AS BytesOut,
SUM(TotalBytes) AS TotalBytes
FROM ' + @SummTableName + '
WHERE ([date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND [date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
AND [Requests] > 0
AND [Action] = 0
GROUP BY [Site]
ORDER BY ' + QUOTENAME(@OrderColumn) + ' DESC;
'
INSERT #Table1
EXEC(@strQuery)
END
DECLARE @TotalRequests bigint
SET @TotalRequests = (SELECT SUM(Requests) FROM #Table1)
IF (@TotalRequests = 0)
SET @TotalRequests = 1
DECLARE @totalBytes bigint
SET @totalBytes = (SELECT SUM(TotalBytes) FROM #Table1)
IF (@totalBytes = 0)
SET @totalBytes = 1
DECLARE @TotalBytesIn bigint
SET @TotalBytesIn = (SELECT SUM(BytesIn) FROM #Table1)
IF (@TotalBytesIn = 0)
SET @TotalBytesIn = 1
DECLARE @TotalBytesOut bigint
SET @TotalBytesOut = (SELECT SUM(BytesOut) FROM #Table1)
IF (@TotalBytesOut = 0)
SET @TotalBytesOut = 1
SELECT TOP(@TopCount)
Site,
Requests,
(CAST(Requests AS decimal) / @TotalRequests) AS RequestRatio,
BytesIn AS BytesIn,
(CAST(BytesIn AS decimal) / @TotalBytesIn) AS BytesInRatio,
BytesOut AS BytesOut,
(CAST(BytesOut AS decimal) / @TotalBytesOut) AS BytesOutRatio,
TotalBytes AS TotalBytes,
(CAST(TotalBytes AS decimal) / @totalBytes) AS TotalByteRatio
FROM #Table1
WHERE [Site] != N'{[23124]}'
UNION ALL
SELECT
N'{[23124]}' As Site,
SUM(Requests),
(CAST(SUM(Requests) AS decimal) / @TotalRequests) AS RequestRatio,
SUM([BytesIn]),
(CAST(SUM([BytesIn]) AS decimal) / @TotalBytesIn) AS BytesInRatio,
SUM([BytesOut]),
(CAST(SUM([BytesOut]) AS decimal) / @TotalBytesOut) AS BytesOutRatio,
SUM([TotalBytes]) AS TotalBytes,
(CAST(SUM([TotalBytes]) AS decimal) / @totalBytes) AS TotalByteRatio
FROM #Table1 t1
WHERE NOT EXISTS
(SELECT * FROM
(SELECT TOP(@TopCount) *
FROM #Table1
WHERE [Site] != N'{[23124]}') t2
WHERE t1.Site = t2.Site)
END