SQL append data based on multiple dates

Question

I have two tables; one contains encounter dates and the other order dates. They look like this:

id  enc_id enc_dt         
1   5      06/11/20       
1   6      07/21/21        
1   7      09/15/21        
2   2      04/21/20                  
2   5      05/05/20       

id  enc_id ord_dt         
1   1      03/7/20        
1   2      04/14/20       
1   3      05/15/20        
1   4      05/30/20                  
1   5      06/12/20
1   6      07/21/21
1   7      09/16/21
1   8      10/20/21
1   9      10/31/21                    
2   1      04/15/20        
2   2      04/21/20       
2   3      04/30/20
2   4      05/02/20
2   5      05/05/20
2   6      05/10/20

The order and encounter date can be the same, or differ slightly for the same encounter ID. I'm trying to get a table that contains all order dates before each encounter date. So the data would like this:

 id  enc_id    enc_dt    enc_key        
    1   1      03/7/20   5       
    1   2      04/14/20  5     
    1   3      05/15/20  5       
    1   4      05/30/20  5
    1   5      06/11/20  5
    1   1      03/7/20   6      
    1   2      04/14/20  6      
    1   3      05/15/20  6      
    1   4      05/30/20  6
    1   5      06/12/20  6
    1   6      07/21/21  6
    1   1      03/7/20   7    
    1   2      04/14/20  7    
    1   3      05/15/20  7       
    1   4      05/30/20  7
    1   5      06/12/20  7
    1   6      07/21/21  7
    1   7      09/15/21  7
    2   1      04/15/20  2
    2   2      04/21/20  2
    2   1      04/15/20  5     
    2   2      04/21/20  5     
    2   3      04/30/20  5
    2   4      05/02/20  5
    2   5      05/05/20  5

Is there a way to do this? I am having trouble figuring out how to append the orders and encounter table for each encounter based on orders that occur before a certain date.

user_12705912 · Answer

You may join the two tables as the following:

SELECT O.id, O.enc_id, O.ord_dt, E.enc_id
FROM
order_tbl O 
JOIN encounter_tbl E
ON O.ord_dt <= E.enc_dt AND
   O.id = E.id

See a demo from db<>fiddle.

SQL append data based on multiple dates

1 Answers