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.
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