La fila más cercana en la otra tabla
Una tabla es una muestra de usuarios y sus compras.
Estructura:
Email | NOMBRE | TRAN_DATETIME (Varchar)
Entonces tenemos el correo electrónico del cliente + Nombre y Apellido + Fecha de transacción
Y la segunda tabla que proviene del segundo sistema contiene a todos los usuarios, sus datos confidenciales y cuándo se registraron en nuestro sistema.
Estructura Simplificada:
Email | InstertDate (varchar)
Mi tarea es contar la diferencia en minutos entre las filas insertadas de la venta (primera tabla) y las filas con los usuarios y sus datos confidenciales.
El problema es que la segunda tabla contiene muchas filas y quiero encontrar la fila más cercana en el tiempo que se insertó en la segunda tabla, porque a veces puede haber una diferencia de minutos (retraso o lo contrario) y a veces puede ser de unos días.
Entonces, para el correo electrónico x, tengo una fila en la primera tabla:
E_MAIL NOMBRE TRAN_DATETIME
p****@****.eu xxx xxx 2021-10-04 00:03:09.0000000
Pero luego tengo 3 filas y la más reciente es la que quiero contar la diferencia:
Correo electrónico 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 <–
Escribí esa consulta, pero no tengo idea de cómo hacer coincidir la fila más cercana en la segunda tabla.
SELECT DISTINCT TOP (100)
,a.[E_MAIL]
,a.[NOMBRE]
,a.[TRAN_DATETIME]
,CASE WHEN b.EMAIL IS NOT NULL THEN ‘SÍ’ ELSE ‘NO’ END AS ‘EXISTE’
,(ABS(CONVERT(INT, CONVERT(Datetime,LEFT(a.[TRAN_DATETIME],10),120))) – CONVERT(INT, CONVERT(Datetime,LEFT(b.[INSTERTDATE],10),120))) as ‘Precisión de la fecha’
FROM [crm].[SalesSampleTable] a
left join [crm].[SensitiveTable] b on a.[E_MAIL]) = b.[EMAIL]
davy.ai
Para encontrar la fila más cercana en la segunda tabla, puedes usar una subconsulta con la función MIN() para obtener la diferencia de tiempo mínima entre la fecha y hora de transacción en la primera tabla y la fecha de inserción en la segunda tabla para cada correo electrónico. Aquí tienes una versión revisada de tu consulta:
En la subconsulta, seleccionamos la primera fila de la segunda tabla donde el correo electrónico coincide y la fecha de inserción es mayor o igual que la fecha y hora de transacción en la primera tabla. Luego ordenamos los resultados por el valor absoluto de la diferencia de tiempo y seleccionamos aquellos con la diferencia de tiempo mínima. Luego utilizamos la función DATEDIFF() para calcular la diferencia en minutos entre las dos fechas y horas. Esto debería darte la fila más cercana en la segunda tabla para cada correo electrónico en la primera tabla.