I have the following data in a SQL table

I want to achieve the following result based on the time difference between 2 records (difference can be 0 and less than 100ms)

I am not a SQL expert and tried to mimic several simlar aproaches and I have a working solution like below

with xvalues as (select date, data_value as 'X' from _prod_data_line where data_id='camTray_x' ), 
yvalues as (select date, data_value as 'Y' from _prod_data_line where data_id='camTray_Y' )
    
select xvalues.date, xvalues.X, yvalues.Y from xvalues left join yvalues on 
      abs(datediff(millisecond, xvalues.date, yvalues.date))<100

Is this doable without the 2 selects ?

CodePudding user response：

This will process the operation with a single scan instead of two, but it is more complex, so (as often is the case) you trade complexity for performance.

; -- see sqlblog.org/cte
WITH cte AS
(
  SELECT date, 
    X = data_value,
    Y = LEAD(CASE WHEN data_id = 'camTray_y' THEN data_value END,1) 
             OVER (ORDER BY date),
    delta = DATEDIFF(MILLISECOND, date, 
        LEAD(CASE WHEN data_id = 'camTray_y' THEN date END,1) 
             OVER (ORDER BY date))
  FROM dbo._prod_data_line
)
SELECT date, X, Y FROM cte WHERE delta <= 100;

Output:

• Example db<>fiddle

Also, this is a simplification, because it assumes no x/y will overlap. If you want to handle those, please provide additional edge cases like that one and those mentioned in the comments, and explain how you want them handled.