Join show later dates only

Question

I have a table that records steps of a drive.
For example:

   Data    - Movimentacao - Produto
2018-01-10 - produzido    -   id1
2018-01-11 - embalado     -   id1
2018-01-12 - despachado   -   id1
2018-01-10 - produzido    -   id2
2018-01-10 - produzido    -   id3
2018-01-11 - embalado     -   id3
2018-01-10 - produzido    -   id4

With the following query I can show which were packaged and not shipped:

SELECT    l.id
FROM      (SELECT id FROM movimentacao WHERE movimento = 'embalado'  ) l
LEFT JOIN (SELECT id FROM movimentacao WHERE movimento = 'despachado') r USING(id)
WHERE     r.id IS NULL

But if a product is repackaged another line is inserted with the "packaged".

   Data    - Movimentacao - Produto
2018-01-10 - produzido    -   id1
2018-01-11 - embalado     -   id1
2018-01-12 - despachado   -   id1
2018-01-13 - embalado     -   id1

Then this product should enter the consultation I mentioned above, but this does not happen because there is already a movement of "dispatch".
I would like to take those records that do not exist "dispatch" after "packing", even if there has previously been some movement of "dispatch". Any suggestions on how to do this?

Answer 1

Whereas there are only the three statuses shown in the example (produzido, embalado, despachado), I suggest you always take the last occurrence of each id. If it is despachado, the item is finished; being embalado, returns in your query; and being produzido, is at the beginning of the process:

-- precisa validar o nome dos campos e da tabela
SELECT * 
FROM tb_movimentacao m1
WHERE m1.data = (SELECT m2.data FROM tb_movimentacao m2 where m2.id = m1.id ORDER BY m2.data desc LIMIT 1)
  AND m1.movimento = 'embalado';

If it helps, there’s an example working in that fiddle.