Good Day all,
I currently have two tables I am trying to join to do data validations between a source of record system and the end system for it.
Currently Table A has account, date, and amount. Table B has account date and amount as well where as the account and amounts "should match" the date can vary because of the system lag and processing until it's updated into the system. I am trying to find the first instance of the matching account and dollar amount in Table B that happens between each period of time in the rows of Table A.
I have an example of each table below and what the expected result should be. Table A has a record for each month with the amount posted. Depending on updates to the system, table B has multiple entries as it records every time a change is made. I am only looking to find the first instance after the date from Table A, which as you can see in the results Table, our first date in table A is 2/22/2019, and our second is 3/22/2019. For Table B I want to find the first date that occurs after 2/22 but only if it occurs before 3/22 for the matching amount in table A. I hope I have provided enough information.
Table A
Table B
account
Date
Amount
account
Date
Amount
12345
2/22/2019
5000
12345
2/23/2019
5000
12345
3/22/2019
5000
12345
2/24/2019
5000
12345
4/22/2019
4800
12345
3/1/2019
5000
12345
5/22/2019
4600
12345
3/23/2019
5000
12345
6/22/2019
4500
12345
5/12/2019
4800
12345
5/23/2019
4600
12345
5/30/2019
4600
12345
6/22/2019
4500
Result
account
Amount
Date_A
Date_B
12345
5000
2/22/2019
2/23/2019
12345
5000
3/22/2019
3/23/2019
12345
4800
4/22/2019
5/12/2019
12345
4600
5/22/2019
5/23/2019
12345
4500
6/22/2019
6/22/2019
... View more