How to select data for the month and previous year?

Question

How to select data for the month and previous year?

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I'm trying to do a search from dates in MySql.

I need to get the total sum of a value where the year and month are smaller than the ones reported in the variable:

SELECT SUM(valor_pag) FROM controle WHERE MONTH(data_paga) < 07 and YEAR(data_paga) <= 2017

But he is ignoring months like: 08/2016, 09/2016, 10/2016, 11/2016, and 12/2016, because the month is conditioned to be less than 07 in MONTH(data_paga) < 07 .

My intention is to get all the records from the previous month back, understand? But the query understands that it should ignore months larger than 7, so that 08/2016, 09/2016, 10/2016, 11/2016 and 12/2016, or even 08/2015, 09/2015, 10/2015, 11 / 2015 and 12/2015 are left out, and so on

What could be wrong?

php mysql sql select

asked by anonymous 15.07.2017 / 23:27

2 answers

C # excel column format How to compare in update

score 1 · Answer 1

1

Only one "OR" is missing there:

SELECT SUM(valor_pag) FROM controle 
    WHERE (MONTH(data_paga) < 07 and YEAR(data_paga) <= 2017) or
          (YEAR(data_paga) < YEAR(now()))

16.07.2017 / 00:43

score 0 · Answer 2

There is another alternative that would be to use the first day of the month and then get only what is earlier, for example using MAKEDATE() :

SELECT SUM(valor_pag)
 FROM controle
  WHERE data_paga < MAKEDATE(YEAR(NOW()), DAYOFYEAR(NOW()) - DAYOFMONTH(NOW()) + 1)

Another option would be to use LAST_DAY() this way:

SELECT SUM(valor_pag)
 FROM controle
  WHERE data_paga <= LAST_DAY(DATE_ADD(NOW(), INTERVAL -1 MONTH))