WHERE WITH DATES

Asked

Viewed 90 times

2

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

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

Currently shows how this: EX client = 100 type1 completion date = 2017-01 completion date = 2017-01

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

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

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'

BENCH:

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;
php mysql

  • If you want, you can use Mysql’s date functions. MONTH(data_conclusao) = MONTH(CURDATE()) AND YEAR(data_conclusao) = YEAR(CURDATE()), so, you will always have the current date without needing to exchange the 2017-01%

  • Now I gotta a Question, yo: what is the structure of your database or table? Which fields should be taken into account in the query?

  • @Not The Real Hemingway Ready Added Bank Structure.

  • @Nottherealhemingway Summary: The data_completion field in TYPE =2 must always be the date of the current month and date of TYPE = 1 may be any month including the current one.

  • @Bonfimjunior present a current tabular result and which you want. So you clarify the question better so that we can help you.

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 has not been tested. Any editing is welcome!

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.