时间戳

首页 > 解决方案 > How to target a record of table B to and Id of Table A based on a time restriction?

sql - How to target a record of table B to and Id of Table A based on a time restriction?

问题描述

Objective: I would like to match a transaction_id to the first note that was inputted in the system within 20 minutes of the transcation_id's timestamp.

Situation: Both tables are linked by email. Email aa@email.com for example as a transaction_id recorded at 3:59 am on jan 1st 2019. I would like to see if a note was inserted within 20 mns. So Note 1 of table 2 should be targeted. Basically the first occurence. For the second transaction (associated to email zz@email.com), no note would be attached to it since the first note insert is > 20mns.

Table 1:

+---------------------+---------------+------------------+
| timestamp           |    email      |   transaction_id |
+---------------------+---------------+---------------- -+
| 2019-01-01 03:59:00 | aa@email.com  |    123           |
| 2018-12-31 09:00:00 | zz@email.com  |    456           |
+---------------------+-------------+--------------------+

Table 2:

+--------------+--------+---------------------+
|    email     |  note  |      timestamp      |
+--------------+--------+---------------------+
| aa@email.com | note 1 | 2019-01-01 04:00:00 |
| aa@email.com | note 2 | 2019-01-01 04:15:00 |
| aa@email.com | note 3 | 2019-01-01 04:20:00 |
| aa@email.com | note 4 | 2019-01-01 04:25:00 |
| aa@email.com | note 5 | 2019-01-01 06:15:00 |
| zz@email.com | note 1 | 2019-01-01 08:15:00 |
| zz@email.com | note 2 | 2019-01-01 08:16:00 |
|              |        |                     |
+--------------+--------+---------------------+

Output:

+---------------------+--------------+----------------+-------+---------------------+--+
|      timestamp      |    email     | transaction_id | note  |   note_timestamp    |  |
+---------------------+--------------+----------------+-------+---------------------+--+
| 2019-01-01 03:59:00 | aa@email.com |            123 | note1 | 2019-01-01 04:00:00 |  |
+---------------------+--------------+----------------+-------+---------------------+--+

What i tried:

SELECT t1.timestamp
    ,t1.email
    ,t1.transaction_id
    ,Emails
    ,Dates
FROM t1
    INNER JOIN 
        (
        SELECT t2.email AS Emails
            ,t2.note AS Notes
            ,t2.timestamp AS Dates
            ,ROW_NUMBER()
                OVER(PARTITION BY t2.email ORDER BY t2.timestamp ASC) AS Top1_note
        FROM t2
        ) AS Subquery 
    ON t1.email=Subquery.Emails

Im not sure what to put as a WHERE or HAVING condition to restrict the dates of the note to 20mns after the transcation date

标签： sqlsql-server

解决方案

您可以使用CROSS APPLY在 20 分钟内获取所有笔记。用于row_number()仅获取（其中一个）最旧的音符。

SELECT *
       FROM table1 t1
            CROSS APPLY (SELECT *,
                                row_number() OVER (ORDER BY timestamp) rn
                                FROM table2 t2
                                     WHERE t2.email = t1.email
                                           AND t2.timestamp >= t1.timestamp
                                           AND t2.timestamp <= dateadd(minute, 20, t1.timestamp)) x
       WHERE x.rn = 1;

db<>小提琴

推荐阅读