Skip to content
Advertisement

Best way to find cancel and substitute match from the table

I have a table which has all order information.

Table

Id is the unique key for the table and it’s grouped by Order_Id (every order for the same product has the same Order_Id). The main thing is that if order is cancelled, it is recorded as a cancelled order (Cancelled? = True) and for following orders, it must have substitute orders. It can be an exact one-to-one match like Id 5 and 6 but it can also be one-to-many (Id 2,3, and 4), many-to-one, or many-to-many. As you can see, Id 1 and 7 are not part of cancelled/substitute match so should be excluded from the match.

My goal is to find cancelled/substitute match from the below table. It can be sql query or stored procedure. I was also thinking of having one another column – Parent_Id to record cancelled Id to substitue Id but it has to be updated from stored procedure as well.

Any ideas? Thanks.

Advertisement

Answer

Validating (all) combinations is potentially very hard as this is essentially a subset sum problem. If you can add some limitations, then it often gets easier.

The solution below has the following limitations:

  1. The order of the cancelled and substitute transactions within an order_id cannot be mixed. The solution adds up transactions within the order_id that come directly after the cancelled transaction in a running total (running sum). When the running total reaches the cancelled quantity, then the transactions are considered a match. Mixing the transaction order will mess up the running total and fail to find a matching quantity.
  2. Within an order_id there cannot be more than one cancelled transaction. This would require a reset of the running total. This can be done, but will introduce more complexity to the solution.

Example of the mixed order I am referring to in limitation 1.

Sample data

The imposed limitations are valid for the sample data: all substitute transactions come directly after the cancelled transaction and there are no multiple cancellations within a single order_id!

Solution

To see the running total in the results and better understand the final solution you can run this query.

This produces:

A minimal version of the solution that gives you just the matches would be the following:

Resulting in:

A version with related quantities and dates could be this:

Resulting in:

Fiddle to see everything in action.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement