Getting Max date from the given two dates in mysql

2
votes

I have a table with below structure and data.

id ean           Control_date qty
1  4046228081410 26.05.2017   568
2  4046228081410 05.06.2017   900

My expected result would be like below 

2  4046228081410 05.06.2017   1468

To acheive this i am using the below query 

 SELECT EAN,Control_date,SUM(Qty) AS Qty FROM mytable WHERE
(STR_TO_DATE(`Control_date`,'%d.%m.%Y') <= STR_TO_DATE('03.06.2017','%d.%m.%Y') 
OR 
STR_TO_DATE(`Control_date`, '%d.%m.%Y') <= DATE_ADD(STR_TO_DATE('03.06.2017', '%d.%m.%Y'), INTERVAL 7 DAY))
AND ean = 4046228081410

Here i need to sum up the qty where control date< today date and control date > today date and should be less than todaydate + 7 days . Here 2nd control date is 05.06.2017 and greater than today date and less than (03.05.2017 +7 days) But always i am getting where contorl date is less than today date.

1  4046228081410 26.05.2017   1468

But i need data with control date 05.06.2017. Any help would be greatly appreciated.

3
mysqldate
The two date conditions are redundant. If the date is less than 7 days from now, it is also less than today. Also, you need a group by (EAN?) and a max on control_date. See answer below - Sloan Thrasher
Fix your data structure! Dates should be stored using the date data type, not a string. - Gordon Linoff

3 Answers

1
votes

You should really fix your date formats. If you stored the value as a date, the query would simply be:

SELECT EAN, MAX(Control_date), SUM(Qty) AS Qty
FROM mytable
WHERE Control_date < CURDATE() + INTERVAL 7 DAYS AND
      Control_date >= CURDATE() AND
      ean = 4046228081410 
GROUP BY ean;

Note: You can use a constant such as '2017-06-03' if you want a constant date. However, your question specifically says the current date.

Just because you have bogus date formats stored in your data doesn't mean you have to use the same format in queries. The expression '2017-06-03' (or DATE('2017-06-03')) is simpler than the more complex STR_TO_DATE() expression.

In your case, bite the bullet and output the date in a correct format, so you can do:

SELECT EAN, MAX(STR_TO_DATE(`Control_date`, '%d.%m.%Y')), SUM(Qty) AS Qty
FROM mytable
WHERE STR_TO_DATE(`Control_date`, '%d.%m.%Y') < CURDATE() + INTERVAL 7 DAYS AND
      STR_TO_DATE(`Control_date`, '%d.%m.%Y') >= CURDATE() AND
      ean = 4046228081410 
GROUP BY ean;
0
votes

You have a typo in your SQL statement, and if you do not want today's date, then use less and greater

SELECT EAN,Control_date,SUM(Qty) AS Qty FROM mytable WHERE
(STR_TO_DATE(`Control_date`,'%d.%m.%Y') **<** STR_TO_DATE('03.06.2017','%d.%m.%Y') 
OR 
STR_TO_DATE(`Control_date`, '%d.%m.%Y') **>** DATE_ADD(STR_TO_DATE('03.06.2017', '%d.%m.%Y'), INTERVAL 7 DAY))
AND ean = 4046228081410
0
votes

A couple of issues in your statement.

  • Date conditions are redundant
  • Need a MAX function on Control_date

  • Need group by n EAN

    SELECT EAN, max(Control_date) AS control_date, SUM(Qty) AS Qty FROM mytable WHERE STR_TO_DATE(Control_date, '%d.%m.%Y') <= DATE_ADD(STR_TO_DATE('03.06.2017', '%d.%m.%Y'), INTERVAL 7 DAY) AND ean = 4046228081410 GROUP BY EAN