CREATE TABLE #tmp_data_updated (

    client_id varchar(max)
    , event_log_id UniqueIdentifier
    , is_updated bit
    , original_actual_date datetime
    , new_actual_date datetime
)
GO

DECLARE @client_id varchar(max)
DECLARE tgcw_cursor CURSOR
FOR
SELECT DISTINCT agency_id_no
FROM #tmp_data1

OPEN tgcw_cursor

DECLARE @end_date datetime
DECLARE @actual_date datetime
DECLARE @event_log UniqueIdentifier
DECLARE @new_actual_date datetime

FETCH NEXT FROM tgcw_cursor INTO @client_id

WHILE @@FETCH_STATUS = 0
BEGIN

        SELECT @end_date = max(end_date)
        FROM #tmp_data1
        WHERE agency_id_no = @client_id

        SELECT @actual_date = actual_date
            , @event_log = event_Log_id
        FROM #tmp_data1
        WHERE end_date is null
            and agency_id_no = @client_id

IF (
    @end_date <= @actual_date
)
    BEGIN

        SET @new_actual_date = DATEADD(second,1,max(@end_date))

        UPDATE #tmp_data1
        SET actual_date = @new_actual_date
        WHERE @actual_date = actual_date
            and @event_log = event_log_id

        INSERT INTO #tmp_data_updated (
            client_id
            , event_log_id
            , is_updated
            , original_actual_date
            , new_actual_date
        )
        VALUES (
            @client_id
            , @event_log
            , 1
            , @actual_date
            , @new_actual_date
        )
    END

    FETCH NEXT FROM tgcw_cursor INTO @client_id

END
CLOSE tgcw_cursor
DEALLOCATE tgcw_cursor