SQL Statement help : combine data from multiple tables

Question

I have 4 tables:

transactions : where the match of buy orders and sell orders in order to have a transaction

transaction_id (PK)| buy_order_id | sell_order_id | transaction_date        | book_name
---------------------------------------------------------------------------------------
2345               | 132          | 456           | 2022-05-16 09:02:50.867 | BOOK1
2211               | 144          | 765           | 2022-05-16 09:20:40.887 | BOOK2
2346               | 136          | 457           | 2022-05-16 10:05:10.045 | BOOK1
2212               | 155          | 766           | 2022-05-16 10:10:50.887 | BOOK2

buy_orders :

order_id (PK)    | placement_date          | book_name
------------------------------------------------------
132              | 2022-05-16 09:02:50.867 | BOOK1     -----> 1st match for BOOK1
133              | 2022-05-16 09:04:40.887 | BOOK1     -----> following incoming order
134              | 2022-05-16 09:05:50.887 | BOOK1
...
144              | 2022-05-16 10:10:40.887 | BOOK2
145              | 2022-05-16 10:10:50.887 | BOOK2     -----> 2nd match for BOOK2
146              | 2022-05-16 10:10:55.887 | BOOK2     -----> following incoming order

sell_orders :

order_id(PK)     | placement_date          | book_name
------------------------------------------------------
456              | 2022-05-16 09:00:00.867 | BOOK1     -----> 1st match for BOOK1
457              | 2022-05-16 09:02:55.887 | BOOK1     -----> following incoming order
458              | 2022-05-16 09:30:50.807 | BOOK1
...
765              | 2022-05-16 09:10:30.587 | BOOK2
766              | 2022-05-16 10:00:40.887 | BOOK2     -----> 2nd match for BOOK2
767              | 2022-05-16 10:12:55.677 | BOOK2     -----> following incoming order

books :

book_id (PK) | book_name
------------------------
23           | BOOK1
56           | BOOK2

The goal is to generate a code every time a transaction happen for a given book combining book_name + transaction_id + first incoming order_id (buy or sell order which came first after a transaction happened).

So, the wished output should be a single column added to tables buy_orders and sell_orders to identify the order_book_code to which every order belong. Every order should have an order_book_code that refer to the last transaction executed until another transaction execute so the order_book_code change.

buy_orders UNION sell_orders:

order_id (PK) | indicator  | placement_date          | book_name | order_book_code
----------------------------------------------------------------------------------
456           | SELL       | 2022-05-16 09:00:00.867 | BOOK1     | BOOK1_2300_023   ----> sell before match
457           | SELL       | 2022-05-16 09:02:00.887 | BOOK1     | BOOK1_2300_023   ----> sell before match
132           | BUY        | 2022-05-16 09:02:50.867 | BOOK1     | BOOK1_2345_457   ----> match for BOOK1
133           | BUY        | 2022-05-16 09:04:40.887 | BOOK1     | BOOK1_2345_457
134           | BUY        | 2022-05-16 09:05:50.887 | BOOK1     | BOOK1_2345_457
458           | SELL       | 2022-05-16 09:30:50.807 | BOOK1     | BOOK1_2345_457

...
765           | SELL       | 2022-05-16 09:10:30.587 | BOOK2     | BOOK2_2200_100  ----> Sell before match
766           | SELL       | 2022-05-16 10:00:40.887 | BOOK2     | BOOK2_2200_100  ----> Sell before match
154           | BUY        | 2022-05-16 10:10:40.887 | BOOK2     | BOOK2_2200_100
155           | BUY        | 2022-05-16 10:10:50.887 | BOOK2     | BOOK2_2212_146  ----> match for BOOK2
156           | BUY        | 2022-05-16 10:10:55.887 | BOOK2     | BOOK2_2212_146
767           | SELL       | 2022-05-16 10:12:55.677 | BOOK2     | BOOK2_2212_146

I was able to collect the concerned data in my attempts but still not able to implement the logic above and combine data in one additional column :

SELECT p.book_name, m.transaction_id, m.transaction_date, t.order_id, t.placement_date
from books p 
OUTER APPLY ( SELECT sell_order_id, buy_order_id, transaction_id, transaction_date, book_name
FROM transactions where book_name = p.book_name ) m 
OUTER APPLY ( SELECT  'SELL' AS indicator, order_id, placement_date, book_name
from sell_orders S 
where s.order_id=m.sell_order_id 
UNION ALL SELECT 'BUY' AS indicator, order_id, placement_date, book_name
from buy_orders B 
where b.order_id=m.buy_order_id) t 
OUTER APPLY ( SELECT book_name FROM books p 
Where t.book_name = p.book_name ) f

How to resolve this logic in an optimal SQL statement ?

This statement was very challenging for me. I hope my explanation was clear. Any help is much appreciated

So what is your question here exactly? Where specifically in your process have you got stuck? What is your attempt so far? Why isn't it working? — Thom A, Jun 01 '22 at 08:18
Thank you for your comment @Larnu. I've updated my post by mentionning the question. For the attempt, to be honest, I was spending a lot of time to implement the logic but still no success (i.e if I post my last attempt it will be so confusing to understand my logic because I was doing wrong unfortunatelly so I preferred to share with you the use cases). I also, shared an SQL statement for a step towards the goal idea. Thanks again — AmyB, Jun 01 '22 at 08:50
Firstly, changing the `UNION` to a `UNION ALL` will be a performance boost; `UNION` is a very expensive operator, and it's impossible for 2 rows in your 2 union'd statements to be the same, as you use different literal values for `indicator`. — Thom A, Jun 01 '22 at 08:56
Is the expected result that you have shown correspond to the sample data at all ? If it is not, please ensure it is the same to avoid any confusion — Squirrel, Jun 02 '22 at 02:32

SQL Statement help : combine data from multiple tables

0 Answers0