WHERE with dates

2

I have this SQL to create group with sales whenever I had two sales of the same client in TYPE = 1 and another in TYPE = 2, both completed in the current month.

But now, what I need is to filter sales with TYPE = 2 in the current month that the other sales of that client in TYPE = 1 can be any other month not just in the current month.

Currently shows like this: EX Client = 100 Type1 Completion Date = 2017-01 End Date = 2017-01

I need it to look like this: EX client = 100 type1 end date = 20176-12 or 2017-01 type2 = end date = 2017-01

Summarized: The date of sale in TYPE = 2 must always be the date of the current month and date of TYPE = 1 can be any month including the current one.

SELECT * FROM WHERE status = 'CONCLUÍDA' and data_conclusao LIKE '2017-01%' and tipo IN ('1', '2') GRUPO by vendas HAVING MIN (tipo) = '1' and MAX (tipo) = '2'

BANK:

CREATE TABLE 'vendas' (
   'Id' int (11) NOT NULL AUTO_INCREMENT,
   'cliente' int (11) NOT NULL,
   'Tipo' varchar (15) NOT NULL,
   'produto' varchar (150) NOT NULL,
   'data_conclusao' date NOT NULL
   'status' int (11);
   PRIMARY KEY ('id)
) ENGINE=MyISAM AUTO_INCREMENT=549 DEFAULT CHARSET=latin1;
    
asked by anonymous 16.01.2017 / 21:19

1 answer

-1

I have an idea:

SELECT * FROM vendas WHERE status = 'CONCLUÍDA' AND (CASE WHEN tipo = 2 THEN (CASE WHEN data_conclusao LIKE '2017-01%' THEN 1 ELSE 0 END) ELSE data_conclusao END) 

The query was not tested. Any edition is welcome!

    
16.01.2017 / 22:31