Hello, 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 and i want to have a sql query to create this kind of table (3rd table).
|date | id| status_from | status_to|
|--- | --- | --- | ---|
|5-Nov-22 | 123456 | To be Assigned | Assigned to Investigate|
|20-Jan-23 | 123456 | Assigned to Investigate (L2) | In Progress (L2)|
|24-May-23 | 123456 | Request for information (L2) | In Progress (L2)|
|8-Nov-22 | 123456 | Assigned to Investigate | In Progress|
|2-Aug-22 | 123456 | Initial | To be Assigned|
|8-Nov-22 | 123456 | Assigned to Investigate | In Progress|
|8-Nov-22 | 123456 | In Progress | Assigned to Investigate (L2)|
|20-Jan-23 | 123456 | In Progress (L2) | Perform QC on RFI (L2)|
|25-Jan-23 | 123456 | Perform QC on RFI (L2) | Request for information (L2)|
|level_inv | from_state | to_state|
|--- | --- | ---|
|Level 1 | To be Assigned | Assigned to Investigate|
|Level 2 | Assigned to Investigate (L2) | In Progress (L2)|
|Level 2 | Request for information (L2) | In Progress (L2)|
|Level 1 | Assigned to Investigate | In Progress|
|Level 1 | Initial | To be Assigned|
|Level 1 | Assigned to Investigate | In Progress|
|Level 1 | In Progress | Assigned to Investigate (L2)|
|Level 2 | In Progress (L2) | Perform QC on RFI (L2)|
|Level 2 | Perform QC on RFI (L2) | Request for information (L2)|
|LEVEL_INV | date | id | status_from | status_to|
|--- | --- | --- | --- | ---|
|L1 | 6.12.2023 | 123456 | In progress | Assigned|
|L2 | 24.05.2023 | 123456 | In progress | Closed|