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
/
25546
< prev
next >
Wrap
Text File
|
2010-06-15
|
2KB
|
62 lines
CREATE PROCEDURE [dbo].[ISA_spUrlfRestrictedSitesRDL]
@FromDate datetime,
@ToDate datetime,
@ReportType varchar(10),
@TopCount int
AS
BEGIN
DECLARE @SummTableName varchar(100)
SET @SummTableName = dbo.fnGetSummaryTableName('tblSiteSummary', @FromDate, @ToDate, @ReportType)
CREATE TABLE #Table1
(
Site varchar(2048),
UrlCategory nvarchar(255),
Requests bigint
)
IF OBJECT_ID (@SummTableName, 'u') IS NOT NULL
BEGIN
DECLARE @strQuery varchar(8000)
SET @strQuery =
'SELECT
o.[Site],
oo.UrlCategoryName AS UrlCategory,
SUM(o.Requests) AS Requests
FROM ' + @SummTableName + ' AS o
INNER JOIN [tblUrlfMetadata] AS oo
ON o.[UrlCategoryId] = oo.[UrlCategoryId]
WHERE (o.[date] >= ' + QUOTENAME(CONVERT(varchar, @FromDate, 126),CHAR(39)) + ' AND o.[date] <= ' + QUOTENAME(CONVERT(varchar, @ToDate, 126),CHAR(39)) + ')
AND (o.[Action] = 4)
AND (o.[UrlCategoryId] >= 0)
GROUP BY o.[Site],
oo.UrlCategoryName
ORDER BY Requests DESC
'
INSERT #Table1
EXEC(@strQuery)
END
DECLARE @TotalRequests bigint
SET @TotalRequests = (SELECT SUM([Requests]) FROM #Table1)
IF (@TotalRequests = 0)
SET @TotalRequests = 1
SELECT TOP(@TopCount)
Site AS Site,
UrlCategory AS UrlCategory,
Requests AS Requests,
(CAST([Requests] AS decimal) / @TotalRequests) AS RequestRatio
FROM #Table1
WHERE [Site] != N'{[23124]}'
UNION ALL
SELECT
N'{[23124]}' AS Site,
N' ' AS UrlCategory,
SUM([Requests]) AS Requests,
(CAST(SUM([Requests]) AS decimal) / @TotalRequests) AS RequestRatio
FROM #Table1 t1
WHERE NOT EXISTS
(SELECT * FROM
(SELECT TOP(@TopCount) *
FROM #Table1
WHERE [Site] != N'{[23124]}') t2
WHERE ((t1.Site = t2.Site) AND (t1.UrlCategory = t2.UrlCategory)))
END