Table A
Registation_key discharge_dt
1001 7/15/07
1002 6/4/07
1003 7/30/071
Table B
REgistration_key ID end_dt value
1001 879 07/11/07 1
1001 888 07/15/07 2
1002 777 06/01/07 5
1002 778 06/01/07 2
1002 779 06/30/07 6
1003 234 07/31/07 3
1003 234 07/31/07 1
1003 235 08/01/07 7
I am trying to join Table A to Table B using the below log . Table A should have one unique mathincg record from Table B
option 1. Using registration_key and discharge_dt , looking for exact matching date( end_dt) in table B , if there is more than one record that matches then select lowest ID
option 2.if there is no record that matches option1 then, serch for the previous record with end_dt in table A less than end_dt table B. if there is more than one record then select lowest ID
option 3. if there is no record option 2 then search the next record that matches .. discharge_dt greater than end_Dt , if there is more than one record then select lowest Id
so basically, I am looking for an exact matching date in the same registration_key .. if the exact dt doesn't exist looking for the previous record and get the most closer dt and if there is no prvious record than look for next record,.
so the output should look like
Table B
Registration_key ID end_dt discharge_dt value
1001 888 07/15/07 7/15/07 2
1002 778 06/01/07 6/4/07 2
1003 234 07/31/07 8/1/07 7
How can I do this conditional join ?
Start Free Trial
