i'm new to SQL and i would need an help.

I have a TAB and I need to find for any item B in the TAB the item A with the closest date. In this case the A with 02.09.2021 04:25:30

CodePudding user response：

WITH CTE(DATE,ITEM)AS
(
    SELECT '20210907 05:02:05' ,    'A'UNION ALL
    SELECT   '20210906 05:01:02' ,  'A'UNION ALL
    SELECT   '20210905 05:00:02' ,  'A'UNION ALL
    SELECT'20210904 04:59:01' , 'A'UNION ALL
    SELECT'20210903 04:58:03' , 'A'UNION ALL
    SELECT'20210902 04:56:55' , 'A'UNION ALL
    SELECT'20210902 04:33:56' , 'B'UNION ALL
    SELECT'20210902 04:25:30' , 'A'
 )
SELECT 
  CAST(C.DATE AS DATETIME)X_DATE,C.ITEM,Q.CLOSEST
  FROM CTE AS C
  OUTER APPLY
  (
    SELECT TOP 1 CAST(X.DATE AS DATETIME)CLOSEST
    FROM CTE AS X
      WHERE X.ITEM='A'AND CAST(X.DATE AS DATETIME)<CAST(C.DATE AS DATETIME)
       ORDER BY CAST(X.DATE AS DATETIME) ASC
 )Q
 WHERE C.ITEM='B'

You can use OUTER APPLY-approach as in the above query. Please also take a look that datetime-column (DATE)is written in the ISO-compliant form

CodePudding user response：

Your data has only two columns. If you want the only the closest A timestamp, then the fastest way is probably window functions:

select t.*,
       (case when prev_a_date is null then next_a_date
             when next_a_date is null then prev_a_date
             when datediff(second, prev_a_date, date) <= datediff(second, date, next_a_date) then prev_a_date
             else next_a_date
        end) as a_date
from (select t.*,
             max(case when item = 'A' then date end) over (order by date) as prev_a_date,
             min(case when item = 'A' then date end) over (order by date desc) as next_a_date
      from t
     ) t
where item = 'B';

This uses seconds to measure the time difference, but you can use a smaller unit if appropriate.

You can also do this using apply if you have more columns from the "A" rows that you want:

select tb.*, ta.*
from t b outer apply
     (select top (1) ta.*
      from t ta
      where item = 'A'
      order by abs(datediff(second, a.date, b.date))
     ) t
where item = 'B';