Get the last record by date from a posting history in mysql

Question

Get the last record by date from a posting history in mysql

Navigation

#1 by (1 votes)

0

I need to get the last donation situation in a given period. I need to group by donation and situation. Add up by situation and order donations by situation, taking only the last.

Tabela LoteRetornoDoacao
Campos: id,dtBaixa,situacaoDoacao,vrTotalLote,num

Tablela Doacao
Campos: id, situacaoDoacao(atual),vrDoacao,idOperadorResponsavel

Tabela Operador
Campos:id, nome,codigo

Tabela DoacaoLoteRetorno
Campos:id, idDoacao,idLoteRetorno

Tabela LoteDoacao
Campos:id, idPartDoador

 Tabela Participante(doador)
 Campos:id,nome,cod

I need to get the last donation situation in a given period. I've tried this and it did not work, get the maximum date value, but mix the other fields:

    SELECT DISTINCT
(CASE 
WHEN lrd.situacaoDoacao=0 THEN "Aberto"
WHEN lrd.situacaoDoacao=1 THEN "Recebido" 
WHEN lrd.situacaoDoacao=2 THEN "Devolvido" 
WHEN lrd.situacaoDoacao=3 THEN "Cancelado" 
WHEN lrd.situacaoDoacao=4 THEN "Confirmado" 
WHEN lrd.situacaoDoacao=5 THEN "Coleta"
ELSE "n" END)AS situacaoLoteRetorno,
lrd.situacaoDoacao,lrd.id,d.situacaoDoacao AS situacaoAtual,d.vr,partDoador.nome,d.codbarras
FROM Doacao d
LEFT JOIN DoacaoLoteRetorno dlr ON dlr.idDoacao=d.id
LEFT JOIN LoteRectoDoacao lrd ON lrd.id=dlr.idLoteRetornoDoacao
LEFT JOIN LoteDoacao ld ON d.idLoteDoacao=ld.id
LEFT JOIN Participante partDoador ON partDoador.id=ld.idPartDoador
GROUP BY dlr.idDoacao HAVING MAX(lrd.dtbaixa) ORDER BY lrd.dtBaixa DESC;

The template for the reports I need to do.

Not even this simple return query.

   SELECT  DISTINCT lrd.dtBaixa,dlr.idDoacao FROM LoteRetornoDoacao lrd
LEFT JOIN DoacaoLoteRetorno dlr ON lrd.id=dlr.idLoteRetornoDoacao
GROUP BY idDoacao ORDER BY lrd.dtBaixa DESC;

What's wrong? I think it's the craze to find that it will be filtered as in excel .... srsr

mysql hibernate

asked by anonymous 30.11.2018 / 01:49

1 answer

Python function error ("is not defined") After triggering a pdf download service on jmeter, can the "badly formatted" response be a pdf? If yes, how?

score 1 · Accepted Answer

I'll leave it here to document the forum.

I'm not going to leave the whole query, because the goal is to get a record of the highest date.

So the first thing to do is a query sorting DESC by date. Then when you cluster, mysql automatically picks up the first record. Example with the BatchTable table

   SELECT a.* FROM (SELECT lcd.*,dlc.idDoacao FROM LoteColetaDoacao lcd
LEFT JOIN DoacaoLoteColeta dlc ON dlc.idLoteColetaDoacao=lcd.id
ORDER BY lcd.dtColeta DESC)a
GROUP BY a.idDoacao;

I hope I have contributed.