CODEWITHSUNDEEP
sqloracle-database
A.Leggett
A.Leggett

Reputation: 61

Oracle SQL - Query for Changes to Database

Currently experiencing an issue. We are trying to build a report where we can see the changes between a field from one moment in time to another. For instance, a small example would be that we want the report to produce their last name 6 months ago and their last name as of today.

Is there a SQL expression that can do this for me? (e.g. add a SQL date parameter to the first last name column where the data is from 6 months ago).

Thank you

Upvotes: 0

Views: 80

Answers (1)

MT0
MT0

Reputation: 168623

Use a flashback query (you will have to ensure the database is set up to support this first and has enough flashback retention to handle 6 months of data):

SELECT COALESCE( current.id, previous.id ) AS id,
       current.name  AS current_name,
       previous.name AS name_six_months_ago
FROM   table_name current
       FULL OUTER JOIN
       (
         SELECT id, name
         FROM   table_name
         AS OF TIMESTAMP ( ADD_MONTHS( SYSTIMESTAMP, -6 ) )
       ) previous
       ON ( current.id = previous.id );

Upvotes: 1

Related Questions