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

Text File | 2010-06-15 | 5KB | 121 lines

CREATE PROCEDURE [dbo].[ISA_spTrimArrayUserSummary]
    @FromDate datetime,
    @ToDate   datetime
AS
BEGIN
    SET NOCOUNT ON;
    IF OBJECT_ID ('tblUserSummary_Daily', 'u') IS NULL
        RETURN
    -- create #table1 empty table copy
    SELECT TOP(0) * INTO #Table1 FROM [tblUserSummary_Daily]
    DELETE FROM [tblUserSummary_Daily]
    OUTPUT DELETED.* INTO #Table1
    WHERE ([Date] >= @FromDate) AND ([Date] < @ToDate)
    SELECT UserName,
        SUM([BytesOut]) AS BytesOut,
        SUM([BytesIn]) AS BytesIn,
        SUM([TotalBytes]) AS TotalBytes,
        SUM([Requests]) AS Requests,
        SUM([AuthorizedFailures]) AS AuthorizedFailures,
        SUM([WebBytesOut]) AS WebBytesOut,
        SUM([WebBytesIn]) AS WebBytesIn,
        SUM([WebTotalBytes]) AS WebTotalBytes,
        SUM([WebRequests]) AS WebRequests,
        [Date] AS Date
    INTO #TableOrig
    FROM #Table1
    GROUP BY [UserName], [Date]
    TRUNCATE TABLE #Table1
    DECLARE @SQLString1 nvarchar(2000) =
    'SELECT *
    FROM (
        SELECT
            [UserName], [BytesOut], [BytesIn], [TotalBytes], [Requests], [AuthorizedFailures], [WebBytesOut], [WebBytesIn], [WebTotalBytes], [WebRequests],[Date]
        FROM (
            SELECT
                [UserName], [BytesOut], [BytesIn], [TotalBytes], [Requests], [AuthorizedFailures], [WebBytesOut], [WebBytesIn], [WebTotalBytes], [WebRequests], [Date],
                ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY '
    DECLARE @SQLString2 nvarchar(2000) =
                    ' DESC) AS rownum
            FROM #TableOrig
            WHERE [UserName] != N''{[23124]}'' AND '
    DECLARE @SQLString3 nvarchar(2000) =
            ' > 0 ) t
        WHERE rownum <= 5000
    ) ttt
    WHERE [UserName] IS NOT NULL
          AND NOT EXISTS (SELECT *
                          FROM #Table1
                          WHERE ttt.[UserName] = [UserName] AND
                                ttt.[Date] = [Date])'
    DECLARE @SQLString nvarchar(4000)
    DECLARE @OrderString nvarchar(200)
    SET @OrderString = '[AuthorizedFailures]'
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    INSERT INTO #Table1
    EXECUTE(@SQLString);
    SET @OrderString = '[Requests]'
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    INSERT INTO #Table1
    EXECUTE(@SQLString);
    SET @OrderString = '[TotalBytes]';
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    INSERT INTO #Table1
    EXECUTE(@SQLString);
    SET @OrderString = '[BytesIn]';
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    INSERT INTO #Table1
    EXECUTE(@SQLString);
    SET @OrderString = '[BytesOut]';
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    INSERT INTO #Table1
    EXECUTE(@SQLString);
    SET @OrderString = '[WebRequests]';
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    INSERT INTO #Table1
    EXECUTE(@SQLString);
    SET @OrderString = '[WebTotalBytes]';
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    INSERT INTO #Table1
    EXECUTE(@SQLString);
    SET @OrderString = '[WebBytesIn]';
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    INSERT INTO #Table1
    EXECUTE(@SQLString);
    SET @OrderString = '[WebBytesOut]';
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    INSERT INTO #Table1
    EXECUTE(@SQLString);
    SET @OrderString = '[Requests] - [WebRequests]';
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    INSERT INTO #Table1
    EXECUTE(@SQLString);
    SET @OrderString = '[TotalBytes] - [WebTotalBytes]';
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    INSERT INTO #Table1
    EXECUTE(@SQLString);
    SET @OrderString = '[BytesIn] - [WebBytesIn]';
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    INSERT INTO #Table1
    EXECUTE(@SQLString);
    SET @OrderString = '[BytesOut] - [WebBytesOut]';
    SET @SQLString = @SQLString1 + @OrderString + @SQLString2 + @OrderString + @SQLString3;
    INSERT INTO #Table1
    EXECUTE(@SQLString);
    ---- now add tail
    INSERT INTO #TABLE1
    SELECT
        N'{[23124]}', SUM([BytesOut]), SUM([BytesIn]), SUM([TotalBytes]), SUM([Requests]), SUM([AuthorizedFailures]), SUM([WebBytesOut] ), SUM([WebBytesIn]), SUM([WebTotalBytes]), SUM([WebRequests]), [Date]
    FROM #TableOrig ttt
    WHERE
        [Requests] IS NOT NULL AND
        NOT EXISTS (SELECT *
                    FROM #Table1
                    WHERE ttt.[UserName] = [UserName] AND
                        ttt.[Date] = [Date] )
    GROUP BY [Date]
    INSERT INTO [tblUserSummary_Daily]
    SELECT *
    FROM #TABLE1
END