Join show later dates only

Question

Join show later dates only

Navigation

#1 by (0 votes)

0

I have a table that records steps of a move. 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 "packed" movement.

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

So this product should get into the query 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 "concalo", even if there has been previously some "dispatch" movement. Any suggestions on how to do this?

mysql sql

asked by anonymous 26.11.2018 / 14:30

1 answer

Is it possible to change the Index of a database table by the following? Doubt about Progress 4GL

score 0 · Answer 1

Since there are only three statuses shown in the example ( produzido , embalado , despachado ), I suggest you always get 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 you help, you have an example working in this fiddle .