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

Text File | 2010-06-15 | 3KB | 78 lines

CREATE PROCEDURE [dbo].[ISA_spAggregateDailyToMonthlySummaries]
    @CurrentMonth datetime,
    @LastDailyToKeep datetime,
    @LastMonthlyToKeep datetime
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON

    DECLARE @CurrentMonthLastDay datetime
    DECLARE @CurrentMonthFirstDay datetime
    SET @CurrentMonthFirstDay = DATEADD(day, 1 - 1 * DATEPART(day, @CurrentMonth), @CurrentMonth)
    SET @CurrentMonthLastDay = DATEADD(day, -1, DATEADD(month, 1, @CurrentMonthFirstDay))

    PRINT   ' 1st day:'
    PRINT @CurrentMonthFirstDay
    PRINT   'last day:'
    PRINT @CurrentMonthLastDay

    BEGIN TRANSACTION
    BEGIN TRY

        DECLARE @AggregSp varchar(300)
        IF EXISTS (SELECT [AggregationSp] FROM [tblPeriodAggregationRegistration])
        BEGIN

            DECLARE @StrSQL varchar(1000)

            DECLARE AggrSp_cursor CURSOR FOR
            SELECT [AggregationSp] FROM [tblPeriodAggregationRegistration]

            OPEN AggrSp_cursor
            FETCH NEXT FROM AggrSp_cursor INTO @AggregSp
            WHILE (@@FETCH_STATUS = 0)
            BEGIN
                SET @StrSQL = @AggregSp +
                ' @CurrentMonthFirstDay = ' + QUOTENAME(@CurrentMonthFirstDay, NCHAR(39)) +
                ',@CurrentMonthLastDay = ' + QUOTENAME(@CurrentMonthLastDay, NCHAR(39)) +
                ',@LastDailyToKeep = ' + QUOTENAME(@LastDailyToKeep, NCHAR(39)) +
                ',@LastMonthlyToKeep = ' + QUOTENAME(@LastMonthlyToKeep, NCHAR(39))

                PRINT 'Executing : ' + @StrSQL
                EXEC (@StrSQL)

                FETCH NEXT FROM AggrSp_cursor INTO @AggregSp
            END
            CLOSE AggrSp_cursor
            DEALLOCATE AggrSp_cursor
        END

        COMMIT TRANSACTION
        PRINT 'Successfully aggregated daily summaries to monthly summaries'
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        PRINT 'Unable to aggregate daily summaries'
        PRINT ERROR_MESSAGE()
        DECLARE
            @ErrorMessage    NVARCHAR(4000),
            @ErrorNumber     INT,
            @ErrorSeverity   INT,
            @ErrorState      INT,
            @ErrorLine       INT,
            @ErrorProcedure  NVARCHAR(200);

        SELECT
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorNumber = ERROR_NUMBER(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE(),
            @ErrorLine = ERROR_LINE(),
            @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

        RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine);
    END CATCH

END