Need a sequencing logic for a table in Oracle SQL Query-CodePudding

I have an order as ORDER_1 which start from MFG_1 and goes to PORT_B. Order moves via different location and in between at some locations it goes through processing AND MAINTENANCE.

Here in this example it goes into 7 Shipments. Shipment Mode is PROCESS if it stays at same location for days.

Target Data

ORDER
ORDER_1
SOURCE_LOCATION=MFG_1
DESTINATION_LOCATION=PORT_B


SHIPMENT    SOURCE_LOCATION      DESTINATION_LOCATION         MODE
SHP_A           MFG_1                      WH_1            TRANSPORT
SHP_B           WH_1                       WH_2            TRANSPORT
SHP_C           WH_2                       WH_2            PROCESS
SHP_D           WH_2                       BB_1            TRANSPORT
SHP_E           BB_1                       BB_1            PROCESS
SHP_F           BB_1                       PORT_A          TRANSPORT
SHP_G           PORT_A                     PORT_B          VESSEL

I need to have sequence number as given. Shipment sequence will be 1 if Order's Source Location is equal to Shipment Source Location (SHP_A) and if Order's destination location is equal to Shipment Destination Location then it will be last Shipment (COUNT(SHIPMENT))

Here I need to have sequence for in between Shipments. Logic is: Sequence 2 will be the Shipment which Source Location is equal to SHP_A's DESTINATION_LOCATION and if there are 2 Shipments starting from SHP_A's DESTINATION_LOCATION then MODE of PROCESS will be given preference and so on.

Expected Sequence

SHIPMENT    SEQUENCE
SHP_A          1
SHP_B          2
SHP_C          3
SHP_D          4
SHP_F          5
SHP_E          6
SHP_G          7

Thank You for your input.

I am not able to find an easy logic for this sequencing.

Here is my Raw Data:

Raw Data

Expected Result:

Expected Result

Result by D R Query enter image description here

CodePudding user response：

There are some issues with the data provided. It is unclear if the main shipment data are within the same table or not. If not then what are joining columns. Also, ORDER is a reserved keyword and it shouldn't be the name of column.
There is a problem with order of events too. The only column to sort it by is column shipment. Luckily it has values that could be ordered by. There is a question what if previous row destination location is not equal to current row source location.
Here is an option that works with your data from the question. Please, take all mentioned above in consideration when trying to get the job done.
With your data as:

WITH
    tbl AS
        (
            Select 'MFG_1' "SHIPMENT_SOURCE_LOCATION", 'PORT_B' "SHIPMENT_DESTINATION_LOCAATION", 'ORDER_1' "AN_ORDER", 'SHP_A' "SHIPMENT", 'MFG_1' "SOURCE_LOCATION", 'WH_1' "DESTINATION_LOCATION", 'TRANSPORT' "MODE" From Dual Union All
            Select 'MFG_1' "SHIPMENT_SOURCE_LOCATION", 'PORT_B' "SHIPMENT_DESTINATION_LOCAATION", 'ORDER_1' "AN_ORDER", 'SHP_B' "SHIPMENT", 'WH_1' "SOURCE_LOCATION", 'WH_2' "DESTINATION_LOCATION", 'TRANSPORT' "MODE" From Dual Union All
            Select 'MFG_1' "SHIPMENT_SOURCE_LOCATION", 'PORT_B' "SHIPMENT_DESTINATION_LOCAATION", 'ORDER_1' "AN_ORDER", 'SHP_C' "SHIPMENT", 'WH_2' "SOURCE_LOCATION", 'WH_2' "DESTINATION_LOCATION", 'PROCESS' "MODE" From Dual Union All
            Select 'MFG_1' "SHIPMENT_SOURCE_LOCATION", 'PORT_B' "SHIPMENT_DESTINATION_LOCAATION", 'ORDER_1' "AN_ORDER", 'SHP_D' "SHIPMENT", 'WH_2' "SOURCE_LOCATION", 'BB_1' "DESTINATION_LOCATION", 'TRANSPORT' "MODE" From Dual Union All
            Select 'MFG_1' "SHIPMENT_SOURCE_LOCATION", 'PORT_B' "SHIPMENT_DESTINATION_LOCAATION", 'ORDER_1' "AN_ORDER", 'SHP_E' "SHIPMENT", 'BB_1' "SOURCE_LOCATION", 'BB_1' "DESTINATION_LOCATION", 'PROCESS' "MODE" From Dual Union All
            Select 'MFG_1' "SHIPMENT_SOURCE_LOCATION", 'PORT_B' "SHIPMENT_DESTINATION_LOCAATION", 'ORDER_1' "AN_ORDER", 'SHP_F' "SHIPMENT", 'BB_1' "SOURCE_LOCATION", 'PORT_A' "DESTINATION_LOCATION", 'TRANSPORT' "MODE" From Dual Union All
            Select 'MFG_1' "SHIPMENT_SOURCE_LOCATION", 'PORT_B' "SHIPMENT_DESTINATION_LOCAATION", 'ORDER_1' "AN_ORDER", 'SHP_G' "SHIPMENT", 'PORT_A' "SOURCE_LOCATION", 'PORT_B' "DESTINATION_LOCATION", 'VESSEL' "MODE" From Dual 
        )

Do something like this to get some additional columns that you will need later as a nested query to select your expected result from:

        Select 
            t.*,
            ROWNUM "RN",
            CASE 
                WHEN SOURCE_LOCATION = SHIPMENT_SOURCE_LOCATION THEN 'START'
                WHEN DESTINATION_LOCATION = SHIPMENT_DESTINATION_LOCAATION THEN 'END'
            ELSE
                FIRST_VALUE(DESTINATION_LOCATION) OVER(PARTITION BY AN_ORDER ORDER BY AN_ORDER, SHIPMENT Rows Between 1 Preceding And 1 Preceding)
            END "PROGRESS"
        From
            tbl t
        Order By
            t.AN_ORDER, t.SHIPMENT
/*  R e s u l t :
SHIPMENT_SOURCE_LOCATION SHIPMENT_DESTINATION_LOCAATION AN_ORDER SHIPMENT SOURCE_LOCATION DESTINATION_LOCATION MODE              RN PROGRESS
------------------------ ------------------------------ -------- -------- --------------- -------------------- --------- ---------- --------
MFG_1                    PORT_B                         ORDER_1  SHP_A    MFG_1           WH_1                 TRANSPORT          1 START    
MFG_1                    PORT_B                         ORDER_1  SHP_B    WH_1            WH_2                 TRANSPORT          2 WH_1     
MFG_1                    PORT_B                         ORDER_1  SHP_C    WH_2            WH_2                 PROCESS            3 WH_2     
MFG_1                    PORT_B                         ORDER_1  SHP_D    WH_2            BB_1                 TRANSPORT          4 WH_2     
MFG_1                    PORT_B                         ORDER_1  SHP_E    BB_1            BB_1                 PROCESS            5 BB_1     
MFG_1                    PORT_B                         ORDER_1  SHP_F    BB_1            PORT_A               TRANSPORT          6 BB_1     
MFG_1                    PORT_B                         ORDER_1  SHP_G    PORT_A          PORT_B               VESSEL             7 END    
*/

The result shows that the order of shipments is also your desired "SEQUENCE". It is in column RN which is selected as pseudo column ROWNUM. Theere is PROGRESS column too where the start and end are selected and in between there is destination location from a previous row.
That could be used to check your conditions (using case expression) and generate the sequence.
Here is the complete code and the result:

SELECT
    AN_ORDER, 
    SHIPMENT,
    Sum(CASE WHEN PROGRESS = SOURCE_LOCATION THEN 1 
             WHEN PROGRESS = 'END' THEN 1
             ELSE 0 
             END) OVER(PARTITION BY AN_ORDER ORDER BY AN_ORDER, SHIPMENT Rows Between Unbounded Preceding And Current Row)   1"SEQUENCE"
FROM
    (
        Select 
            t.*,
            ROWNUM "RN",
            CASE 
                WHEN SOURCE_LOCATION = SHIPMENT_SOURCE_LOCATION THEN 'START'
                WHEN DESTINATION_LOCATION = SHIPMENT_DESTINATION_LOCAATION THEN 'END'
            ELSE
                FIRST_VALUE(DESTINATION_LOCATION) OVER(PARTITION BY AN_ORDER ORDER BY AN_ORDER, SHIPMENT Rows Between 1 Preceding And 1 Preceding)
            END "PROGRESS"
        From
            tbl t
        Order By
            t.AN_ORDER, t.SHIPMENT
    )
/*  R e s u l t :
AN_ORDER SHIPMENT   SEQUENCE
-------- -------- ----------
ORDER_1  SHP_A             1 
ORDER_1  SHP_B             2 
ORDER_1  SHP_C             3 
ORDER_1  SHP_D             4 
ORDER_1  SHP_E             5 
ORDER_1  SHP_F             6 
ORDER_1  SHP_G             7
*/

Please consider this as an option that you should think about taking into consideration all posssible problems. This code will result with some repeating sequences if your data is not in perfect state.
Regards...

CodePudding user response：

Use a hierarchical query:

SELECT shipment, sequence
FROM   (
  SELECT t.*,
         LEVEL AS sequence,
         MAX(LEVEL) OVER (PARTITION BY rowid) AS max_seq
  FROM   table_name t
  START WITH shipment_source = source_location
  CONNECT BY
         PRIOR an_order = an_order
  AND    PRIOR destination_location = source_location
  AND    PRIOR ROWID != ROWID
)
WHERE sequence = max_seq;

Which, for the sample data:

CREATE TABLE table_name (shipment_source, shipment_destination, an_order, shipment, source_location, destination_location, "MODE") AS
  SELECT 'MFG_1', 'PORT_B', 'ORDER_1', 'SHP_A', 'MFG_1',  'WH_1',   'TRANSPORT' FROM DUAL UNION ALL
  SELECT 'MFG_1', 'PORT_B', 'ORDER_1', 'SHP_B', 'WH_1',   'WH_2',   'TRANSPORT' FROM DUAL UNION ALL
  SELECT 'MFG_1', 'PORT_B', 'ORDER_1', 'SHP_C', 'WH_2',   'WH_2',   'PROCESS'   FROM DUAL UNION ALL
  SELECT 'MFG_1', 'PORT_B', 'ORDER_1', 'SHP_D', 'WH_2',   'BB_1',   'TRANSPORT' FROM DUAL UNION ALL
  SELECT 'MFG_1', 'PORT_B', 'ORDER_1', 'SHP_E', 'BB_1',   'BB_1',   'PROCESS'   FROM DUAL UNION ALL
  SELECT 'MFG_1', 'PORT_B', 'ORDER_1', 'SHP_F', 'BB_1',   'PORT_A', 'TRANSPORT' FROM DUAL UNION ALL
  SELECT 'MFG_1', 'PORT_B', 'ORDER_1', 'SHP_G', 'PORT_A', 'PORT_B', 'VESSEL'    FROM DUAL

Outputs:

SHIPMENT	SEQUENCE
SHP_A	1
SHP_B	2
SHP_C	3
SHP_D	4
SHP_E	5
SHP_F	6
SHP_G	7

fiddle