Include strings in BETWEEN and List missing values in SQL query

Question

Include strings in BETWEEN and List missing values in SQL query

Navigation

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

4

Next. Imagine that I have in the bank in the field of paid months the following launches: 2015-03, 2015-04, 2015-7, 2015-9. If we stipulated a period for example: from 2015-02 until today (2015-9). What months are missing, ie, which months have not yet been paid.

The query I'm submitting here brings in the months INSIDE of the period. But what I need is NOT months paid, but SIM PAYMENTS and IN of the period. That is, 2015-2, 2015-5, 2015-6, 2015-8.

 SELECT pagamentos.idPagamentos, pagamentos.mesReferencia FROM pagamentos, planosclientes
 WHERE
        pagamentos.idPlanoClientes = planosclientes.idPlanosClientes AND
        pagamentos.idPlanoClientes = 8 AND
        pagamentos.mesReferencia BETWEEN DATE_FORMAT(planosclientes.dataInstalacao, '%Y%-%m') AND DATE_FORMAT(CURRENT_DATE(), '%Y%-%m')
 ORDER BY pagamentos.mesReferencia

How to do this?

mysql sql

asked by anonymous 24.09.2015 / 14:50

2 answers

how to make a substring in an int in PHP Calculate lexicographically letter of a string

score 1 · Answer 1

One possible solution is to use DATEADD , taking a month off dataInstalacao and adding a month on CURRENT_DATE() .

SELECT pagamentos.idPagamentos, pagamentos.mesReferencia 
FROM pagamentos, planosclientes
WHERE pagamentos.idPlanoClientes = planosclientes.idPlanosClientes 
   AND pagamentos.idPlanoClientes = 8 
   AND pagamentos.mesReferencia 
      BETWEEN FORMAT(DATEADD(mm,-1,planosclientes.dataInstalacao), 'yyyy-MM') 
      AND FORMAT(DATEADD(mm,1,CURRENT_DATE()), 'yyyy-MM') ;

UPDATE 2

Function DATE_FORMAT and DATE_ADD (for MYSQL):

BETWEEN DATE_FORMAT(DATE_ADD(planosclientes.dataInstalacao, -1 INTEVAL MONTH), '%Y-%M') 
    AND DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 MONTH), '%Y-%M')

However, BETWEEN includes the limits, ie it would not be necessary to make any date adjustments.

But I noticed that you are doing between varchar which will not work.

So, you should do CAST for dates of values to be compared and for that the code would look like this:

AND CAST(CONCAT(pagamentos.mesReferencia, '01') as DATE)
   BETWEEN CAST(CONCAT(FORMAT(planosclientes.dataInstalacao), '%Y-%m'), '01') as DATE) 
   AND CURRENT_DATE()

score 0 · Answer 2

As far as I understand, you want < those > < ------ Period ------ > < and those > right? come on:

     SELECT pagamentos.idPagamentos, pagamentos.mesReferencia FROM pagamentos, planosclientes
 WHERE
        pagamentos.idPlanoClientes = planosclientes.idPlanosClientes AND
        pagamentos.idPlanoClientes = 8 AND
        pagamentos.mesReferencia < DATE_FORMAT(planosclientes.dataInstalacao, '%Y%-%m') AND 
        pagamentos.mesReferencia > DATE_FORMAT(CURRENT_DATE(), '%Y%-%m')
 ORDER BY pagamentos.mesReferencia;

In other words, the smaller < that dataInstalacao and the largest > that the curdate () (current day) so we will have the so called "extremes"