"pivot" table by datediff

I have the following data in a SQL table

date data_id data_value
2022-05-02 11:17:10.9033333 camTray_x 4.989
2022-05-02 11:17:10.9033333 camTray_y 1.308
2022-05-02 11:17:14.0966667 camTray_x 1.469
2022-05-02 11:17:14.1066667 camTray_y 2.845

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

date X Y
2022-05-02 11:17:10.9033333 4.989 1.308
2022-05-02 11:17:14.0966667 1.469 2.845

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 ?

>Solution :

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:

date X Y
2022-05-02 11:17:10.9033333 4.989 1.308
2022-05-02 11:17:14.0966667 1.469 2.845

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.

Leave a Reply