I currently have a basic asset tracking system with the following structure:
As you can see, I have a Many-To-Many table, which is called TB_HISTORICO_ATIVO . In it has all the history in which the asset passed, so I have the following statuses:
- Stock
- Client
- Maintenance
- Burned
The problem is occurring at the time I pull the report for customer .
Example:
An asset number X was in the customer on 01/01/2010, but was for maintenance on 01/01/2012, this implies that this asset is no longer on the customer but on maintenance , but when I make my query, it always takes the date 01/01/2010, because it is in the table and has the client code.
My select:
SELECT
a.CODIGO as 'codigo_ativo',
a.NUMERO_ATIVO,
a.valor_compra,
a.obs as 'obs_ativo',
p.CODIGO as 'codigo_produto',
p.descricao as 'produto',
h.data_evento,
h.obs as 'obs_historico',
c.codigo as 'codigo_cliente',
c.descricao as 'cliente'
from TB_HISTORICO_ATIVO H
left outer join TB_ATIVO a on H.ATIVO = a.CODIGO
left outer join TB_PRODUTO p on a.PRODUTO = p.CODIGO
left outer join TB_CLIENTE c on H.CLIENTE = c.CODIGO
where H.CLIENTE = 2