April 4, 2006 at 11:21 am
I have 2 tables each from a different data source in a call flow. A data record is inserted in the first table that has a timestamp which occurs prior to the timestamp on the same record in the second table. I'm trying to find a way to correlate the records.
Assuming that every other data element is constant, I have only the time stamps with which to compare. Assume there is a 1 to 1 relationship between the records in both tables. Can anyone think of a function that would compare the timestamps and correlate the records?
Thanks-
g.
April 5, 2006 at 2:54 pm
Say Table1 looks like this:
Time1 Key1
<Binary> 1
<Binary> 2
<Binary> 3
<Binary> 4
<Binary> 5
and Table2 looks like this:
Time2 Key2
<Binary> 1
<Binary> 2
<Binary> 3
<Binary> 4
<Binary> 5
where Time1 and Time2 are timestamps.
The following method will correlate the rows by their relative timestamps:
declare @k1 int,
@k2 int,
@t1 timestamp,
@t2 timestamp
declare c1 cursor for
select Key1, Time1
from Table1
order by Time1
declare c2 cursor for
select Key2, Time2
from Table2
order by Time2
open c1
open c2
fetch next from c1 into @k1, @t1
while @@fetch_status=0
begin
fetch next from c2 into @k2, @t2
print '1: ' + cast(@k1 as varchar(10)) + ' 2: ' + cast(@k2 as varchar(10))
fetch next from c1 into @k1, @t1
end
close c1
close c2
deallocate c1
deallocate c2
NOTE: You can do whatever you need to do in the loop. This particular loop depends on your assertion that the tables are identical except for their timestamps.
Does anyone know how to do this without cursors?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply