SQL Server - How to get 2 values before and after timestamp from another table-CodePudding

I was just wondering if anyone can help me with this problem.

I have two tables - Table 1 & Table 2.

What I'm trying to do is to find 2 timestamps before and after 'date' from Table 1, so everything highlighted in pink in Table 2.

How can I do this in Microsoft SQL Server? ideally without using CTE. Unfortunately, CTE is not supported by Tableau.

Thank you in advance.

CodePudding user response：

Here is one option using a CROSS JOIN, datediff() and row_number()

Select Date
      ,Value
      ,ID
 From  (
        Select A.Date
              ,A.Value
              ,B.ID
              ,RN1 = row_number() over (order by datediff(second,a.date,b.date) desc) 
              ,RN2 = row_number() over (order by datediff(second,a.date,b.date) asc) 
         From Table2  A
         Cross Join Table1 B  
       ) A
 Where RN1 in (2,3)
   or  RN2 in (2,3)
 Order By Date

Results

CodePudding user response：

If you want to select this specific range of rows in table2, the simplest approach might be union all:

(
    select top (3) t2.date, t2.value
    from table1 t1
    inner join table2 t2 on t2.date < t1.date
    order by t2.date desc 
)
union all
(
    select top (3) t2.date, t2.value
    from table1 t1
    inner join table2 t2 on t2.date > t1.date
    order by t2.date
)

Both suqueries start from the reference date in table1, and then fetch the previous (resp. next) 3 records in table2, using order by and fetch. The combination of the two datasets gives you the result you want.

With a very small table1 and with the relevant index in place on table2(date), both subqueries should execute very efficiently. Adding column value to the table2 index might further help performance, by making the index covering for the query.