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/25566

Text File | 2010-06-15 | 1KB | 41 lines

CREATE PROCEDURE [dbo].[ISA_spUrlfActivity]
    @ArrayName nvarchar(MAX),
    @ServerName nvarchar(MAX),
    @FromDate datetime,
    @ToDate datetime,
    @CollectionTime datetime
AS
BEGIN
    IF OBJECT_ID ('dbo.[tblUrlfSummary_Daily]', 'u') IS NULL RETURN
    SELECT
        @ArrayName As ArrayName,
        meta.[UrlCategoryName],
        urlfid.[UrlCategoryHits],
        urlfid.[UrlCategoryBlocked],
        @CollectionTime As CollectionTime
    FROM
    (
        SELECT
            result.[UrlCategoryId] As UrlCategoryId,
            sum(result.[UrlCategoryHits]) As UrlCategoryHits,
            sum(result.[UrlCategoryBlocked]) As UrlCategoryBlocked
        FROM
        (
            SELECT
                [UrlCategoryId],
                sum([Requests]) As UrlCategoryHits,
                case
                    when [action]=8 then sum([Requests])
                    else 0
                end as UrlCategoryBlocked
            FROM  dbo.[tblUrlfSummary_Daily]
            WHERE ([date] >= @FromDate AND [date] <= @ToDate AND [UrlCategoryId] >= 0) AND (isnull(dbo.tblUrlfSummary_Daily.ServerName,@ServerName)=@ServerName)
            GROUP BY [UrlCategoryId], [Action]
        ) As result
        GROUP BY result.[UrlCategoryId]
    ) As urlfid
    INNER JOIN [tblUrlfMetadata] AS meta
    ON urlfid.[UrlCategoryId] = meta.[UrlCategoryId];
END