0
Currently I have a basic asset control system with the following structure:
As you can see, I have a Many-To-Many table, which is called TB_HISTORICO_ATIVO. It has all the history the asset has spent, so I have the following status:
- Stockpile
- Client
- Maintenance
- Burned
The problem is occurring at the time I pull the report by client.
Example:
An asset number X was on 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, when I do my query, it always takes the date 01/01/2010, because it appears 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
probably your query should be returning the two records, and you always want the last status following the chronological order. Right?
– Rodrigo K.B
No, he’s just returning the record that’s tied up with the client, but in reality, he’s returning the past status, in this case, it wasn’t to return anything.
– Phoenix209
I’m lost in this issue, rs, I’m not able to solve. I can even solve inside the application using LINQ and everything, but I want to leave this task of filtering the data within the query itself.
– Phoenix209
A solution to simplify sqls can be to create a column in TB_ATIVO date of the last history (DT_ULTIMO_HISTORICO) and by Trigger feed this column, otherwise make sqls with subselect and max to obey the latest history
– Motta
Interesting!... This solution to create the field and work with a Rigger. But, what would select look like with MAX()? I tried N ways and none worked.
– Phoenix209
Ball show @Motta, I created this field, migrated the information, and in this select I passed above, I simply implemented in the filter a comparison by update date! (y) Thanks.
– Phoenix209
with 'max" would be something like .... select * from historico active Where = 123 data = (select max(data) from historico active Where = 123)
– Motta
In this case, I wouldn’t have the asset parameter, just the client.
– Phoenix209