Include extremes in BETWEEN and List missing values in SQL query

Question

Next. Imagine I have in the bank in the paid months field the following releases: 2015-03, 2015-04, 2015-7, 2015-9. If we set a period for example: from 2015-02 to today (2015-9). Which months are missing, that is, which months have not yet been paid.

The query I present here brings paid months WITHIN period. But what I need ARE NOT the months paid, but YES the UNPAID MONTHS and WITHIN period. Ie 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 it in this case?

Answer 1

A possible solution is to use DATEADD, except a month of 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, that is, it would not be necessary to make any date adjustments.

But I realized you’re doing between varcharwhat will not work.

So you must do the CAST for 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()

Answer 2

As far as I understand it, you want to < 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;

That is to say the smallest datastalacao and the largest > than the kurdish() (present day) so we will have the so-called "extremes"