MYSQL - Largest date and number of days of the current date

Asked

Viewed 34 times

0

I have a table where reports of calls are stored. Each report may have one or more work dates.

The main fields would be:

nr_relatório,
dt_trabalho

I need to list only those reports whose working date of the last day (most recent date) is greater than 21 days and display the number of days from today until this most recent date. To become something like:

nr_relatorio  |  ultima_jornada  | Nr_dias
  1234            02-09-2020         26
  4567            03-09-2020         27

I tried using Max and Datediff but it’s not working properly. I even managed to list the journeys, but then the number of days he took from the comparison with another date. If anyone can give me a light, I’d appreciate it.

select 
nr_relatorio,
dt_trab,
max(dt_trab),
DATEDIFF(Curdate(), dt_trab)
from RELATORIO_TECNICO_SERVICO 
where (DATEDIFF(Curdate(), dt_trab) > 21) 
group by nr_relatorio
order by dt_trab
  • Try to remove the field dt_trab from your selection list because, it seems to me, you are only interested in max(dt_trab) for each nr_report.

No answers

Browser other questions tagged

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