[SOLVED] Merging 2 datasets with timestamp as unique identifiers (but they are not the same)

Merging 2 datasets with timestamp as unique identifiers (but they are not the same)

I have 2 historian tag data. I do openquery to get these data in table. The data is like this:

TAG1:

timestamp1 string1

TAG2:

timestamp2 string2

I would like to combine this into one table

timestamp1 string1 string2

The problem is that Timestamp1 and Timestamp2 does not match. I need to get string1 and string2 together in table. Is there any option to solve this issue? Also the difference in timestamps can differ in circumstances. (it can be 100ms to 5sec)

Example:

Tag1 table:

   10/09/2018 22:05:00          "hello"

   10/09/2018 22:10:00          "heyhey"

Tag2 table:

  10/09/2018 22:05:28          "azeryy"

  10/09/2018 22:10:04          "qwerty"

Result table:

  10/09/2018 22:05:00          "hello"      "azeryy"

  10/09/2018 22:10:00          "heyhey"     "qwerty"

Solution

In the future it would be helpful if you can post sample data. Since you are new around here I put this together for you so you can see an example of this.

declare @Tag1 table
(
    SomeDate datetime
    , SomeValue varchar(20)
)

insert @Tag1 values
('10/09/2018 22:05:00', 'hello')
, ('10/09/2018 22:10:00', 'heyhey')

declare @Tag2 table
(
    SomeDate datetime
    , SomeValue varchar(20)
)

insert @Tag2 values
('10/09/2018 22:05:28', 'azeryy')
, ('10/09/2018 22:10:04', 'qwerty')

Now we can focus on the task at hand which is very loosely merging this together. Row_Number is very helpful for this. You can read more about ROW_NUMBER here. https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017

select t1.SomeDate
    , t1.SomeValue
    , t2.SomeValue
from
(
    select *
        , RowNum = ROW_NUMBER() over(order by SomeDate desc)
    from @Tag1
) t1
join
(
    select *
        , RowNum = ROW_NUMBER() over(order by SomeDate desc)
    from @Tag2
) t2 on t2.RowNum = t1.RowNum
order by t1.SomeDate