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

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

CREATE PROCEDURE [dbo].[ISA_spUserSummaryMonthly]
    @CurrentMonthFirstDay datetime,
    @CurrentMonthLastDay datetime,
    @LastDailyToKeep datetime,
    @LastMonthlyToKeep datetime
AS
BEGIN
    SET NOCOUNT ON;
-- move dailies to monthlies
    SELECT
        [UserName] AS [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],
        @CurrentMonthFirstDay AS [Date]
    INTO #TableOrig
    FROM dbo.tblUserSummary_Daily
    WHERE (Date >= @CurrentMonthFirstDay) AND
          (Date <= @CurrentMonthLastDay)
    GROUP BY
        [UserName]
    -- create #table1 empty table copy
    SELECT TOP(0) * INTO #Table1 FROM #TableOrig
    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 (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])'
    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]), @CurrentMonthFirstDay
    FROM #TableOrig ttt
    WHERE
        NOT EXISTS (SELECT *
                    FROM #Table1
                    WHERE ttt.[UserName] = [UserName])
    INSERT INTO dbo.tblUserSummary_Monthly
    SELECT *
    FROM #Table1
-- cleanup dailies
    DELETE FROM [ISA_RS_Db].[dbo].[tblUserSummary_Daily]
    WHERE (Date < @LastDailyToKeep)
-- cleanup monthlies
    DELETE FROM [ISA_RS_Db].[dbo].[tblUserSummary_Monthly]
    WHERE (Date < @LastMonthlyToKeep)
END