I need to put together a phpmyadmin SQL to list a table with the companies and contracts that I have to pay in the current month and that if I do not already pay the list

Navigation
0
SELECT  a.cod_contrato, a.cod_anunciante, a.presupuesto, b.cod, b.mcuota, date_format(b.mesdpago,'%d-%m-%Y') AS mesdpago, c.cod, c.nom, c.cor1, c.tel1 FROM espacios_publicitarios.anunciantes AS c INNER JOIN espacios_publicitarios.contratos AS a ON a.cod_anunciante = c.cod INNER JOIN espacios_publicitarios.ppto_cuotas AS b ON a.presupuesto = b.cod left JOIN espacios_publicitarios.pagos AS d ON d.contrato = a.cod_contrato   WHERE  b.mesdpago  LIKE '%2018-10%'

I have the following tables

  • advertisers (Company information)
  • contracts (active contracts)
  • ppto_cuotas (Quotas of budgets)
  • payments (Resgitrod d payments of each corresponding installment)

I just need the SQL, with which I currently have all ready so they have already paid ..

So far I can not relate the other tables with payments what I want is to show me (Those who have a quota in the current month and have not paid yet)

Thank you in advance.

    
asked by Luis Alfredo SV 01.11.2018 в 12:51
source

2 answers

0

You can use the MONTH () function: It returns the month of a date, in the range of 1 to 12.

Documentation: link

Usage:

One option would be to convert the date into a month and pass it as a parameter the current date also converted into a month.

Script: select * from tabla where MONTH(fecha) = MONTH(now())

    
answered by 01.11.2018 в 13:12
0

In your table you must have a status or something similar, with values like paid or must . In WHERE b.mesdpago LIKE '%2018-10%' you add AND estado = debe

    
answered by 01.11.2018 в 13:37
Doubts with the if in access 2013 When a date type field is saved in the database, it is saved with a date from a previous day