SHARE
TWEET

Untitled

a guest Sep 16th, 2019 92 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE #tmp_data_updated (
  2.    
  3.     client_id varchar(max)
  4.     , event_log_id UniqueIdentifier
  5.     , is_updated bit
  6.     , original_actual_date datetime
  7.     , new_actual_date datetime
  8. )
  9. GO
  10.  
  11. DECLARE @client_id varchar(max)
  12. DECLARE tgcw_cursor CURSOR
  13. FOR
  14. SELECT DISTINCT agency_id_no
  15. FROM #tmp_data1
  16.  
  17. OPEN tgcw_cursor
  18.  
  19. DECLARE @end_date datetime
  20. DECLARE @actual_date datetime
  21. DECLARE @event_log UniqueIdentifier
  22. DECLARE @new_actual_date datetime
  23.  
  24. FETCH NEXT FROM tgcw_cursor INTO @client_id
  25.  
  26. WHILE @@FETCH_STATUS = 0
  27. BEGIN
  28.  
  29.         SELECT @end_date = max(end_date)
  30.         FROM #tmp_data1
  31.         WHERE agency_id_no = @client_id
  32.    
  33.         SELECT @actual_date = actual_date
  34.             , @event_log = event_Log_id
  35.         FROM #tmp_data1
  36.         WHERE end_date is null
  37.             and agency_id_no = @client_id
  38.  
  39. IF (
  40.     @end_date <= @actual_date
  41. )
  42.     BEGIN
  43.        
  44.         SET @new_actual_date = DATEADD(second,1,max(@end_date))
  45.  
  46.         UPDATE #tmp_data1
  47.         SET actual_date = @new_actual_date
  48.         WHERE @actual_date = actual_date
  49.             and @event_log = event_log_id
  50.  
  51.         INSERT INTO #tmp_data_updated (
  52.             client_id
  53.             , event_log_id
  54.             , is_updated
  55.             , original_actual_date
  56.             , new_actual_date
  57.         )
  58.         VALUES (
  59.             @client_id
  60.             , @event_log
  61.             , 1
  62.             , @actual_date
  63.             , @new_actual_date
  64.         )
  65.     END
  66.  
  67.     FETCH NEXT FROM tgcw_cursor INTO @client_id
  68.  
  69. END
  70. CLOSE tgcw_cursor
  71. DEALLOCATE tgcw_cursor
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top