I have a two tables Transition_Log and GRDM. I need to create using SQL query (oracle dialect) which will connect these 2 tables and will : ID, LEVEL_INV, latest date. The connection beetwen these 2 tables is (key) Transition_Log.status_from = GRDM.state_from AND Transition_Log.status_to = GRDM.state_to
The result is on 3rd table
Transition_Log
| DATE | ID | status_from | status_to |
|---|---|---|---|
| 5.10.2022 | 123456 | Open | In progress |
| 20.01.2023 | 123456 | Open | Closed |
| 24.05.2023 | 123456 | In progress | Closed |
| 5.11.2023 | 123456 | Assigned | In progress |
| 6.12.2023 | 123456 | In progress | Assigned |
GRDM
| LEVEL_INV | Comment | state_from | state_to |
|---|---|---|---|
| L1 | xxxxxxx | Open | In progress |
| L2 | xxxxxxx | Open | Closed |
| L2 | xxxxxxx | In progress | Closed |
| L1 | xxxxxxx | Assigned | In progress |
| L1 | xxxxxxx | In progress | Assigned |
Result
| LEVEL_INV | DATE | ID | status_from | status_to |
|---|---|---|---|---|
| L1 | 6.12.2023 | 123456 | In progress | Assigned |
| L2 | 24.05.2023 | 123456 | In progress | Closed |
Select GRDM.level_INV, MAX(TRL.DATE), TRL.ID, TRL.status,TRL.status_to
FROM TRANSITION_LOG as TRL
inner join GRDM on TRL.status_from=grdm.state_from and TRL.status_to=grdm.state_to
group by LEVEL_INV, ID
What should add to recreate this 3rd table ??