title: Declare new variable and input date based on rank
body: <p>I want to 'clean' a dataset and declare a new variable, then input a date based on rank.</p>
  
  <p>My dataset looks like this:</p>
  
  <pre><code>+-----+--------------+------------+-------+
  | ID  |  Start_date  |  End_date  |  Rank |
  +-----+--------------+------------+-------+
  | a   | May '16      | May '16    |     5 |
  | a   | Jun '16      | Jul '16    |     4 |
  | a   | Jul '16      | Aug '16    |     3 |
  | a   | Aug '16      | NULL '16   |     2 |
  | a   | Sept '16     | NULL '16   |     1 |
  +-----+--------------+------------+-------+
  </code></pre>
  
  <p>I basically want to input the start date of rank 1 into the end date of rank 2 or say input start 5 into end 6 (always -1).</p>
  
  <p>Have written the following to select into a tempory table and rank based on id and date:</p>
  
  <pre><code>SELECT 
     [Start_Date] as 'start'
    ,[End_Date] as 'end'
        ,[Code] as 'code'
        ,[ID] as 'id'
    ,rank() over (partition by [id] order by [Start_Date]) as 'rank'
  INTO #1
  FROM [Table]
  ORDER BY [id]
  </code></pre>
  
  <p>Its the following part that doesn't work ...</p>
  
  <pre><code>DECLARE new_end 
  BEGIN select [#1].[start] into new_end FROM [#1]
  WHERE (
      ([#1].[rank] = 1) 
      AND ([#1].[end] IS NULL)
      )   
  </code></pre>