MYSQL - add column and calculate days late of a date and sort by the longest delay

Asked

Viewed 154 times

0

Hello,

Suppose I have a table tb_fatura with the following fields and values:

id | transacao_id | valor    | vencimento
1  | 1            | 1109.97  | 2020-02-19
2  | 1            | 10,      | 2019-05-01

I want somar the column valor, calcular the dias em atraso do vencimento and order by the greatest delay.

With the query below I can even get some kind of result, but still not what I need:

SELECT *, SUM(valor) AS total, DATEDIFF(CURDATE(), vencimento) as atraso
FROM tb_fatura
WHERE transacao_id = 1
GROUP BY transacao_id

This brings me the following result:

id | transacao_id | valor   | vencimento | total   | atraso
1  | 1            | 1109.97 | 2020-02-19 | 1119.97 | 91

This does not suit me because I need to order for the biggest delay and even adding order by, I couldn’t! Doing this is possible ?

mysql sql

  • I guess you need to add up the days of delay, don’t you? something like SUM(DATEDIFF(CURDATE(), vencimento))

  • I do not need to add up the days in delay, I only need to get the biggest delay! the sum must be made only for the value!

  • then MAX doesn’t work?

1 answer

-1


Responding based on the comment, just use a MAX where calculates the days difference (delay):

SELECT SUM(valor) AS total, MAX(DATEDIFF(CURDATE(), vencimento)) as atraso
  FROM tb_fatura
 WHERE transacao_id = 1
 GROUP BY transacao_id;

You can see it working on http://sqlfiddle.com

  • who negative can explain what is bad, if the answer shows a solution to the problem, and still shows a working example?

  • It worked for me, thank you very much for the answer! And honestly I do not understand the reason for the negative question and answer! I don’t think that question and/or answer is so low as to be disqualified!

Browser other questions tagged mysql sql

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