IF conditional within SQL query

Navigation
0

I have the following query: 

$string = "
SELECT DISTINCT(idClientes), nome FROM clientes WHERE idClientes IN ( 
 SELECT idClientes FROM planosclientes WHERE idPlanosClientes IN (
   SELECT distinct(idPlanoClientes) FROM pagamentos WHERE mesReferencia NOT IN ('".$mesReferencia."') 
    ) and DATEDIFF (CURDATE(), CONCAT(DATE_FORMAT(CURDATE(),'%Y,%m' ),'-', diaVencimento)) > ".$dias."
    UNION
    SELECT idPlanosClientes FROM planosclientes WHERE idPlanosClientes NOT IN                                                (SELECT distinct(idPlanoClientes) FROM pagamentos)
           ) ORDER BY nome";

It turns out that if the diaVencimento field is greater than the current day, in php date('d') , the expression $mesReferencia that receives a value of type date('Y-m') must be from the previous month. That is date('Y-m') less 1 month.

How to make this conditional?

Tables in question: 

CREATE TABLE clientes (
  idClientes int(10) unsigned NOT NULL AUTO_INCREMENT,
  tipoClientes char(1) NOT NULL DEFAULT '',
  nome varchar(100) NOT NULL,
  cpf char(11) DEFAULT '',
  cnpj char(14) DEFAULT '',
  email varchar(100) DEFAULT '',
  telefone varchar(11) NOT NULL DEFAULT '',
  celular varchar(11) DEFAULT '',
  bloqueado char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (idClientes)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

CREATE TABLE pagamentos (
  idPagamentos int(10) unsigned NOT NULL AUTO_INCREMENT,
  idPlanoClientes int(10) NOT NULL,
  idAdmins int(1) NOT NULL,
  mesReferencia char(7) NOT NULL,
  dataPgto date NOT NULL,
  valorPgto double NOT NULL,
  multa double NOT NULL,
  juros double NOT NULL,
  desconto double NOT NULL,
  totalPago double NOT NULL,
  formaPgto char(2) NOT NULL,
  observacao text,
  PRIMARY KEY (idPagamentos)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE planosclientes (
  idPlanosClientes int(10) unsigned NOT NULL AUTO_INCREMENT,
  idClientes int(10) NOT NULL,
  idPlanos int(10) NOT NULL,
  valorCombinado double NOT NULL,
  diaVencimento varchar(2) NOT NULL DEFAULT '0',
  dataInstalacao date NOT NULL DEFAULT '0000-00-00',
  observacao text,
  login varchar(25) NOT NULL DEFAULT '',
  senha varchar(25) DEFAULT '',
  bloqueado char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (idPlanosClientes)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
    
asked by anonymous 30.09.2015 / 18:08

1 answer

1

After the questions asked in the comments the final query for what you need would be this: 

$string = "
  SELECT c.idClientes, c.nome
    FROM clientes c 
           INNER JOIN planosclientes PC ON (c.idClientes = pc.idClientes)
           INNER JOIN pagamentos p ON (pc.idPlanosClientes = p.idPlanoClientes)
   WHERE p.mesReferencia != (CASE WHEN pc.diaVencimento>EXTRACT(DAY from CURDATE()) 
                                  THEN DATE_FORMAT((CURDATE() - INTERVAL '1' MONTH), '%Y-%m')
                                  ELSE DATE_FORMAT(CURDATE(), '%Y-%m') 
                              END)
     AND DATEDIFF(CURDATE(), CONCAT(DATE_FORMAT( CURDATE(), '%Y-%m' ), '-', pc.diaVencimento)) > ".$dias."
   ORDER BY c.nome ";

The condition you are looking for is done by the command CASE , in your case: 

(CASE WHEN pc.diaVencimento>EXTRACT(DAY from CURDATE()) 
      THEN DATE_FORMAT((CURDATE() - INTERVAL '1' MONTH), '%Y-%m')
      ELSE DATE_FORMAT(CURDATE(), '%Y-%m') 
  END)

See that I replace the use of date('Y-m') with the bank function DATE_FORMAT( CURDATE(), '%Y-%m' )

UNION eliminates customers who have plans and did not make payments, so JOIN usage meets this filter.

Any problem just write there in the comments.

    
30.09.2015 / 20:29
Conditional to change activity BootsFaces Error javax.faces.FacesException: Expression error