Hi,
I have two tables buy and sell, need to compare each sell records in buy table and find which buy is sold. sell needs to first compare the date, if buy happened on same day then it should first taken into calculation, remaining goes to very first buy transaction for the same product.
Buy Table
BuyTranID Product Date Buy
Buy1 ABC 3/1/2020 200
Buy2 ABC 3/1/2020 1000
Buy3 ABC 3/10/2020 600
Buy4 ABC 3/18/2020 200
Sell Table
SellTranId Product Date Sell
Sell1 ABC 3/10/2020 700
Sell2 ABC 3/10/2020 500
Sell3 ABC 3/18/2020 100
requirement is produce the data in below format
Holding Report - Query
BuyTranID Product Date Buy SellTranId Product Date Sell hold
Buy3 ABC 3/10/2020 600 Sell1 ABC 3/10/2020 700 -100
Buy1 ABC 3/1/2020 200 Sell1 ABC 3/10/2020 100 100
Buy1 ABC 3/1/2020 100 Sell2 ABC 3/10/2020 500 -400
Buy2 ABC 3/1/2020 1000 Sell2 ABC 3/10/2020 400 600
Buy2 ABC 3/1/2020 600
Buy4 ABC 3/18/2020 200 Sell3 ABC 3/18/2020 100 100
Buy4 ABC 3/18/2020 100
Any suggestion will very helpful
Thanks in advance.