The nearest row in the other table-CodePudding

One table is a sample of users and their purchases.

Structure:

Email | NAME | TRAN_DATETIME (Varchar)

So we have customer email FirstName&LastName Date of transaction

and the second table that comes from second system contains all users, they sensitive data and when they got registered in our system.

Simplified Structure:

Email | InstertDate (varchar)

My task is to count minutes difference between the rows insterted from sale(first table)and the rows with users and their sensitive data. The issue is that second table contain many rows and I want to find the nearest in time row that was inserted in 2nd table, because sometimes it may be a few minutes difeerence(delay or opposite of delay)and sometimes it can be a few days.

So for x email I have row in 1st table:

E_MAIL          NAME        TRAN_DATETIME
p****@****.eu   xxx xxx     2021-10-04 00:03:09.0000000

But then I have 3 rows and the lastest is the one I want to count difference

Email           InstertDate
p****@****.eu   2021-05-20 19:12:07
p****@****.eu   2021-05-20 19:18:48
p****@****.eu   2021-10-03 18:32:30  <--

I wrote that some query, but I have no idea how to match nearest row in the 2nd table

SELECT DISTINCT TOP (100) 
          ,a.[E_MAIL]
          ,a.[NAME]
          ,a.[TRAN_DATETIME]
          ,CASE WHEN b.EMAIL IS NOT NULL THEN 'YES' ELSE 'NO' END AS 'EXISTS'
          ,(ABS(CONVERT(INT, CONVERT(Datetime,LEFT(a.[TRAN_DATETIME],10),120))) - CONVERT(INT, CONVERT(Datetime,LEFT(b.[INSERTDATE],10),120)))  as 'DateAccuracy'
          
      FROM [crm].[SalesSampleTable] a 
      left join [crm].[SensitiveTable] b on a.[E_MAIL]) = b.[EMAIL]

CodePudding user response：

Totally untested: I'd need sample data and database the area of suspect is the casting of dates and the datemath.... since I dont' know what RDBMS and version this is.. consider the following "pseudo code".

We assign a row number to the absolute difference in seconds between the dates those with rowID of 1 win.

WTIH CTE AS (
SELECT A.*, B.* row_number() over (PARTITION BY A.e_mail
                                   ORDER BY abs(datediff(second, cast(Tran_dateTime as Datetime), cast(InsterDate as DateTime)) desc) RN
FROM [crm].[SalesSampleTable] a 
LEFT JOIN [crm].[SensitiveTable] b
     on a.[E_MAIL] = b.[EMAIL])
SELECT * FROM CTE WHERE RN = 1