Get the latest record by date from a mysql release history

Asked

Viewed 771 times

0

I need to pick up the latest donation situation at a certain time. I need to group by donation and by situation. Add 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 status within a certain period. I tried that and it didn’t work, it takes the maximum value of the date, but it mixes 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;

Follows the model of the reports I need to do. Agrupado Detalhado

Nor this simple query correct return.

   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 is the habit of thinking that it will be filtered as in excel.... srsr

mysql hibernate

1 answer

1


Guys I’ll leave here to document the forum.

I will not leave the whole query,because the goal is to get a record meeting the question of higher date.

So the first thing to do is a query ordering by DESC date. Then when grouping, mysql automatically takes the first record. Example with the 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 contributed.

Browser other questions tagged mysql hibernate

You are not signed in. Login or sign up in order to post.