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
/
25213
< prev
next >
Wrap
Text File
|
2010-06-15
|
2KB
|
61 lines
CREATE PROCEDURE [dbo].[ISA_spTrimArraySoftBlockSummary]
@FromDate datetime,
@ToDate datetime
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID ('tblSoftBlockSummary_Daily', 'u') IS NULL
RETURN
-- Move to #TrimmedSummaryEntries all relevant entries
SELECT TOP(0) * INTO #TrimmedSummaryEntries FROM [tblSoftBlockSummary_Daily]
DELETE FROM [tblSoftBlockSummary_Daily]
OUTPUT DELETED.* INTO #TrimmedSummaryEntries
WHERE ([Date] >= @FromDate) AND ([Date] < @ToDate)
-- Prepare to trimming: group, ignoring date
SELECT TOP(0) * INTO #TrimmedEntriesGrouped FROM [tblSoftBlockSummary_Daily]
INSERT INTO #TrimmedEntriesGrouped
SELECT DISTINCT
[Site],
[URLCategoryId],
[UserName],
SUM([Requests]) AS [Requests],
@ToDate AS [Date]
FROM #TrimmedSummaryEntries
GROUP BY [Site], [URLCategoryId], [UserName]
-- Trim the result table to the maximum of 10000 records
INSERT INTO [tblSoftBlockSummary_Daily]
SELECT
[Site],
[URLCategoryId],
[UserName],
[Requests],
@ToDate AS [Date]
FROM (
SELECT
[Site], [URLCategoryId], [UserName], [Requests]
FROM
(
SELECT
[Site], [URLCategoryId], [UserName], [Requests],
ROW_NUMBER() OVER (ORDER BY [Requests] DESC) AS rownum
FROM #TrimmedEntriesGrouped
) TopSites
WHERE rownum <= 10
UNION ALL
SELECT
N'{[23124]}' as [Site],
0 as [URLCategoryId],
N' ' as [UserName],
SUM([Requests]) as [Requests]
FROM
(
SELECT
[Site], [URLCategoryId], [UserName], [Requests],
ROW_NUMBER() OVER (ORDER BY [Requests] DESC) AS rownum
FROM #TrimmedEntriesGrouped
) OtherSites
WHERE rownum > 10
) AllSites
WHERE [Requests] IS NOT NULL
END