title: SQL query to get previous and new column detail from a table
body: <p>I have a table that has both audit data for addresses-</p>
  <pre><code>ADDRESS_ID          EFFECTIVE_START_DATE   EFFECTIVE_END_DATE   ADDRESS_LINE_1  ADDRESS_LINE_2      TOWN        Person_id
  300000009360612     2020-04-01              4712-12-31          Box 14          ADAM SELLERS ST     Mirror      157
  300000009360612     2000-03-27              2020-03-31          Box 13                              Mirror      157
  </code></pre>
  <p>I want the previous value and current value for the columns that change. For example, the output should look like -</p>
  <pre><code>ADDRESS_ID          Changed Attribute           Previous_Value              Current_Value
  300000009360612     Effective_start_Date        2000-03-27                      2020-04-01
  300000009360612     Effective_end_Date          2020-03-31                      4712-12-31
  300000009360612     ADDRESS_LINE_1              Box 13                          Box 14
  300000009360612     ADDRESS_LINE_2                                              ADAM SELLERS ST
  </code></pre>
  <p>I have tried -</p>
  <pre><code> select * from ( 
   SELECT  
            address_id     , 
            'Effective_Start_Date'   Changed_Attribute  ,
            cast(Effective_Start_Date as varchar(40))                                 current_value
              , LAG(cast(effective_start_date as varchar(40)), 1, 0) OVER (partition by address_ID, effective_start_date ORDER BY last_update_date) Previous_Value
   FROM     fusion.per_addresses_f_ 
   ORDER BY last_update_date DESC) 
  where current_value &lt;&gt; Previous_Value
  </code></pre>
  <p>The above query is not giving the right output. It is giving me the below output-</p>
  <pre><code>address_id              Changed_Attribute               current_value           Previous_Value
   300000009360612        Effective_start_Date            2020-04-01              
  </code></pre>
  <p>i.e. i am not getting any value in previous_value.</p>