Get associated row from history table

Question

If you have a table which holds history (updates, deletes) from another table, how would you pull an associated update row if it comes after a delete operation?

Table has the following columns not related to the data
ID int, -- Identity
OP varchar -- the delete or update operation coded as 'D' or 'U'
Removed_Date -- the date the row was removed from the 'Live' table

I've tried something like this
Select *
from historytable as table1
join historytable as table2 on table1.Removed_Date like table2.Removed_Date

which results in multiple rows (ten or more) of duplicates for one row in the table.

Answer 1

Hi,

if you are absolutely sure the timestamps are identical for a delete and corresponding update you can do this:

select *
from historytable as table1
inner join historytable as table2 on table1.Removed_Date = table2.Removed_Date 
where table1.OP = 'D' 
and table2.op = 'U'

If the timestamps are not identical, and you have no other way of determining which UPDATE follows which DELETE, all you can do is retrieve the first UPDATE after a DELETE.

select t1.*, 
  (select top 1 id 
  from historytable t2 
  where t2.Removed_Date > t1.Removed_Date
  and t2.code = 'U')
from historytable t1 where code = 'D'

-Edoode

Answer 2

Here is what I ended up doing in Perl:

while (<>) {
@cols = split(/,/);
$removeby = $cols[3];
if ($cols[1] eq "D" and $removeby!~/ottr|hksys/) {
if ($hold) {
@oldCols = split(/,/,$hold);
$oldRemoveBy = $oldCols[3];
if ($removeby eq $oldRemoveBy) {
print $hold;
print;
}
$hold = "";
}
$hold = "";
} else {
$hold = $_;
}
}

It's not a fool-proof solution, but it seems to work.