Mysql query - How to select products that were returned before they were purchased using sql only

Question

I have the following table of a mysql product movement database which contains the following fields:

id | prod_id | prod_name | movimento_tipo | data_transaction
1  | idProd1 | tv        | ordemCompra    | 1-jan-2018
2  | idProd1 | tv        | shipped        | 3-jan-2018
3  | idProd2 | pc        | shipped        | 4-jan-2018
4  | idProd2 | pc        | ordemCompra    | 5-jan-2018
5  | idProd3 | sapato    | return         | 8-jan-2018
6  | idProd3 | sapato    | ordemCompra    | 7-jan-2018
7  | idProd4 | camisa    | ordemCompra    | 10-jan-2018
8  | idProd4 | camisa    | ordemCompra    | 12-jan-2018

The tv product (idProd1) is correct. It had a purchase order on 1 jan and Shipping happened on 3 jan. The pc product (idProd2) has an error as Shipping came before (4-jan) than the purchase (5-jan). In the same way the shoe had a return before purchase. The shirt (idProd4 ) had 2 purchase orders but there was no return between these two purchases.

My question is what would be the sql query to capture each of these errors. I cannot use any language in the backend (php or python for example) to filter these situations.

Answer 1

A solution involves connecting the table to itself, through the product code, and using data_transaction to find the error instances, i.e., situations that should not occur in reality.

As is the case of, for example:

• Return a product that has never been purchased;
• Ship a product before the purchase order enters the system;
• Return the product twice without having been re-exported in the meantime.

Here is just one example, how to identify the products for which the shipment (Shipping) has a pre-purchase date. Using this example, I think I can create the logic to identify the remaining error situations.

SELECT 'Expedicao antes da compra' AS SituacaoErro,
       t1.prod_id,
       t1.prod_nome,
       t1.movimento_tipo,
       t1.data_transaction AS DataDeExpedicao,
       t2.data_transaction AS DataDeCompra
  FROM tbl_tabela t1 -- Expedicao
 INNER JOIN tbl_tabela t2 -- Compra
    ON t2.prod_id = t1.prod_id
 WHERE t1.movimento_tipo = 'shipped'  
   AND t2.movimento_tipo = 'ordemCompra'
   AND t2.data_transaction > t1.data_transaction  -- A data da ordem de compra é posterior à data de compra.

There are several alternatives to get the same result (can user EXISTS, GROUP BY together with HAVING, etc). This is just one example.

--Update after new comment

Extrapolating the situation to 3 moves, could add another INNER JOIN or maybe do as follows:

SELECT *
  FROM 
  (
     SELECT prod_id,
            MAX(CASE WHEN movimento_tipo = 'ordemCompra' THEN data_transaction END)) AS DataOrdemCompra, -- Assumindo que pode ser vendido apenas uma vez
            MIN(CASE WHEN movimento_tipo = 'shipped' THEN data_transaction END)) AS DataExpedicao,  -- Data da primeira expedicao
            CASE WHEN movimento_tipo = 'return' THEN data_transaction END) AS DataDevolucao -- Data da devolucao
       FROM tbl_tabela
      GROUP BY prod_id
  ) Sumario
 WHERE Sumario.DataOrdemCompra > Sumario.DataExpedicao
    OR Sumario.DataDevolucao > Sumario.DataOrdemCompra
    OR Sumario.DataDevolucao > Sumario.DataExpedicao