Select with due dates

Question

How to resolve this issue.

ID      status      valor    vencimento    clienteID

1220    Pendente    37,00    2019-02-20    2888
3146    Pendente    37,00    2019-03-20    2888
3147    Pendente    37,00    2019-04-07    2888
3148    Pendente    37,00    2019-05-15    2888
3149    Pendente    37,00    2018-06-20    2888

I’m in need of help in the following case.

I have this table above. I need to get the data of the accounts due up to the current month and more of the following month of this client, regardless of the due date, which would be the following:

37,00    2019-02-20

37,00    2019-03-20

37,00    2019-04-07

37,00    2018-06-20

I am not able to do this, I have tried several ways, but list the current year and does not include the past years.

Answer 1

thank you very much for the reply. I managed to solve, after many researches, as below, taking the example of Been

$currentdia_ = date("d");

SELECT * FROM test WHERE maturity <= DATE(NOW()) + INTERVAL 1 MONTH + INTERVAL (DAY(LAST_DAY(DATE(NOW()))-$currentdia_) DAY;

Answer 2

Whereas you are referring to the SQL language.

SELECT valor, vencimento
    FROM sua_tabela
    WHERE status = 'Pendente' AND
        vencimento < date_trunc('month', CURRENT_DATE + interval '2 month');

Depending on the DBMS that is using the function to calculate the first day of the second month may be different.

Answer 3

You did not inform which bank you are using and whether the command will be from a specific language/environment, I created an example with Mysql 5.6, although you also did not inform, I considered the status field as indicative if the release was paid (liquidado) or is overdue (pendente), created some data, fictitious and the SQL command:

Creating the schema (Mysql v5.6)

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(6) unsigned NOT NULL,
  `status` varchar(20) NOT NULL,
  `vencimento` date,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `test` (`id`, `status`, `vencimento`) VALUES
  ('1220', 'pendente', '2018-02-20'),
  ('3146', 'pago', '2018-03-20'),
  ('3147', 'pendente', '2019-03-20'),
  ('3148', 'pago', '2019-03-20'),
  ('3149', 'pendente', '2019-03-22'),
  ('3150', 'pendente', '2019-03-25'),
  ('3151', 'pendente', '2019-04-07'),
  ('3152', 'pendente', '2019-05-15');

---

Query assembly (whereas the first record was created in 2014) #1

SELECT 
    * FROM test
WHERE 
    vencimento BETWEEN STR_TO_DATE('2014-01-01', '%Y-%m-%d') 
    AND STR_TO_DATE('2019-04-30', '%Y-%m-%d')
    AND status='pendente';

Upshot:

| id   | status   | vencimento |
| ---- | -------- | ---------- |
| 1220 | pendente | 2018-02-20 |
| 3147 | pendente | 2019-03-20 |
| 3149 | pendente | 2019-03-22 |
| 3150 | pendente | 2019-03-25 |

---

Obs.:
The example serves to Voce select any date range, and easily adapted to qq bank.

See working on DB Fiddle