SQL join two tables with no key column but need to join with date filter column

Question

I want to join two tables. Tables are following below,

Table A:

Batch_ID INT,
Start_Dt DATE,
Expiry_Dt DATE

Table B:

Purchase_Dt DATE

I need to get two oldest batch code for each purchase date. Purchase date should be greater than or equal to start_dt and expiry_dt should be less than or equal to purchase date.

Does this answer your question? [Using LIMIT within GROUP BY to get N results per group?](https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) — Akina, Nov 11 '20 at 05:22
Which of the 2 columns determines the age of the batch code? — Tim Biegeleisen, Nov 11 '20 at 05:22

score 1 · Accepted Answer · answered Nov 11 '20 at 05:22

1

You can try using row_number()

select Purchase_Dt,Batch_ID from
(
select Purchase_Dt,Batch_ID,row_number() over(partition by Purchase_Dt order by batch_id desc) as rn 
from B join A on Purchase_Dt>=start_dt and Purchase_Dt<=Expiry_Dt
)f where rn=1

answered Nov 11 '20 at 05:22

Fahmi

37,315
5
22
31

SQL join two tables with no key column but need to join with date filter column

1 Answers1