CODEWITHSUNDEEP
sqlsql-serverdatetimesubquerylateral-join
GolangMan
GolangMan

Reputation: 21

Select the last to a certain date record from the dependent table

In SQL Server I have 2 tables

table_a

card_number reissue_date
1           15.02.2017 00:00:00
2           01.02.2017 00:00:00
3           01.01.2017 00:00:00

table_b

card_number timestamp               limit
1           01.01.2017 00:00:00     9999
1           01.02.2017 00:00:00     100000
1           01.03.2017 00:00:00     25000
2           01.01.2017 00:00:00     10001
3           01.03.2017 00:00:00     5000
3           01.04.2017 00:00:00     0

Expected Result

card_number limit
1           100000
2           10001

I tried many options in this, I came close to a solution, but I could not display the column "limit", what is the best way to solve this problem?

My wrong decision

SELECT table_b.card_number, Max(timestamp)
FROM   table_b LEFT JOIN table_a
  ON ( table_b.card_number = table_a.card_number
   AND table_b.timestamp < table_a.reissue_date )
WHERE  table_a.reissue_date IS NOT NULL
GROUP  BY table_b.card_number;

It is necessary to make a selection of the latest by date records of the table_b table, but not larger than the date in table_a

The working solution I came to at the moment, I'm not sure if it works correctly, but with my initial data it gave the desired result

SELECT card_number, 
       Max(timestamp), 
       Max(limit) AS timestamp 
FROM   table_b 
WHERE  table_b.timestamp < (SELECT reissue_date 
                            FROM   table_a 
                            WHERE  card_number = table_b.card_number) 
GROUP  BY card_number;

Upvotes: 0

Views: 159

Answers (1)

GMB
GMB

Reputation: 222442

In SQL Server, we can use a row-limiting lateral join to bring the latest record in the second table prior to the timestamp of the first table:

select a.card_number, b.*
from table_a a
cross apply (
    select top (1) b.*
    from table_b b 
    where b.card_number = a.card_number and b.timestamp < a.reissue_date 
    order by b.timestamp desc
) b

This also eliminates rows that have no match, which is consistent with your data.

Upvotes: 1

Related Questions