time stamps

  • 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.

  • 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?


    Steve Eckhart

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply