Rolling back time in T_Address

Has anyone written some SQL to use the TA_AUDIT_TRAIL to reconstruct older version of records in T_Address.

That is using TA_AUDIT_TRAIL can we re-construct all customer addresses as they were in the past?

I'd like my set to have a set of record for each t_address  that has been updated.  Grouping the changes into a single new record by the date that appears in the TA_AUDIT_TRAIL table.  SO If the audit has three unique datetimes for a particular address.  The T_Address record would appear in my resulting data set 4 times.  One for each of the audit recorded upgrade date/times and once for the current state of the t_address record.

I've done the basic grouping here for TA_AUDIT_TRAIL.  Have not yet figured out connecting this with T_Address.  Rather than working this through, I'm wondering if someone has already done this.

The reason we would like to do this is to be able to look at the addresses as of when a customer made a specific contribution or order.  (Not the current default address.)

Thoughts?  Code Chunks? ... 

select tat.alternate_key , tat.date

 

 

, max(case when tat.column_updated = 'street1' then old_value else null end) as street1

, max(case when tat.column_updated = 'street2' then old_value else null end) as street2

, max(case when tat.column_updated = 'city' then old_value else null end) as city

, max(case when tat.column_updated = 'state' then old_value else null end) as 'state'

, max(case when tat.column_updated = 'postal_code' then old_value else null end) as 'postal_code'

, max(case when tat.column_updated = 'primary_ind' then old_value else null end) as 'primary_ind'

from TA_AUDIT_TRAIL as tat

 

 

where

 

 

table_name = 'T_Address' and old_value is not null

group

 

 

by tat.alternate_key, tat.date

 

--Tom

 

[edited by: Tom Brown at 5:38 PM (GMT -6) on 15 Oct 2013]