Last post Sep 22, 2012 12:58 PM by Prysson
Sep 22, 2012 12:58 PM|Prysson|LINK
I have two tables that represent a historical data they both have a “src” column that represents the primary key in the primary table for that data.
The primary tables would be
The history tables basically match the original with a "src" column indicating the source primary key record from the primary table.
tbl_user_hist tbl_email_hist______________ ______________user_pk email_pksrc_user_pk src_email_pkusername emailemail_pk tstamptstamp
so if you had an email record of primary key 1 and you changed the email from firstname.lastname@example.org to email@example.com and then again to firstname.lastname@example.org
your tbl_email_hist would look like this
email_pk src_email_pk email tstamp1 1 email@example.com 2012-01-01 04:06:282 1 firstname.lastname@example.org 2012-03-01 04:06:283 1 email@example.com 2012-07-01 04:06:28
And lets say that bobs original user name was rsmith and then it was changed to bsmith and then back to rsmith2. But these user name changes do NOT NECESSARILY coincide with the timestamps of the email changes.
SO similarly the user history table would look like
tbl_user_histUser_pk src_user_pk username email_pk tstamp1 1 rsmith 1 2012-01-01 04:08:282 1 bsmith 1 2012-02-01 04:01:283 1 rsmith2 1 2012-05-01 04:05:28
SO what I WANT is to create a query that will be able to find the correct email history record based on time stamp and the related email_pk record…meaning what was the value of that field at the time the user record was changed. So in some way I need to find the record in the email hist table where the src_email_pk matches the email_pk in the user hist table but the tstamp for that email history record is the highest date while still <= the timestamp in the user table.
In the end my Data would look like this
Username email username_timestampRsmith firstname.lastname@example.org 2012-01-01 04:08:28Bsmith email@example.com 2012-02-01 04:01:28Rsmith2 firstname.lastname@example.org 2012-05-01 04:05:28
In other words reflecting the value of the email column at the time the username record was modified/created.