SQL Server SQL Get record from table 2 that immediately follows record in table 1 and only return that record

14ifxucb 于 2023-04-10 发布在其他

关注(0)|答案(1)|浏览(188)

I have data like so and for some reason it's just not clicking for me right now:
| cid | cmt_date | comment |
| ------------ | ------------ | ------------ |
| 1 | 2023-01-01 | LTR |
| 2 | 2023-02-01 | RTR |

Here is an example of table 2

pid	pmt_date	payment
1	2023-03-01	100

I would want to see the following back:

cid	comment_date	comment	pmt_date	pmt
2	2023-02-01	RTR	2023-03-01	100

Each table has about 400,000 records in them, there could be 20 records in table 1 and only 1 record in table 2.

I did something like this:

SELECT 
    t1.cid, t1.cmt_date AS comment_date, t1.comment, 
    t2.pmt_date, t2.payment AS pmt
FROM
    table1 t1
INNER JOIN 
    table2 t2 ON t2.pmt_date > t1.cmt_date 
              AND t1.pt_no = t2.pt_no

pt_no is like an invoice number and is used for a join on the same invoice.

sql-server

来源：https://stackoverflow.com/questions/75945021/sql-get-record-from-table-2-that-immediately-follows-record-in-table-1-and-only

1条答案

按热度按时间

2cmtqfgy1#

You can use CROSS APPLY with a subquery to get just 1, "most recent", row from table2 for each row in table1:

SELECT t1.cid, t1.cmt_date AS comment_date, t1.comment, t2.pmt_date, t2.payment AS pmt
FROM table1 t1
CROSS APPLY (
    SELECT TOP 1 *
    FROM table2
    WHERE pmt_date > t1.cmt_date AND pt_no = t1.pt_no
    ORDER BY pmt_date DESC
) t2

Change the ordering to ORDER BY pmt_date ASC to get one row with the closest date to t1.cmt_date (I wasn't sure which option you wanted). You might also want to use >= in the subquery comparison (again I can't be sure of this).

赞(0）回复(0）举报 2023-04-10

我来回答

SQL Server SQL Get record from table 2 that immediately follows record in table 1 and only return that record

1条答案

相关问题

热门标签

最新问答