Mysql select consecutive days

Asked

Viewed 44 times

1

All right?! I have selected below to bring the days where there is event 99. If there is more than one event 99 on the same day it brings the day only once. I would like to improve this select to bring only when the days are consecutive.

SELECT dt_ini from ocorrencias where cd_motorista = '20427' and cd_status = 99 and dt_ini between date_add('2019-10-21', interval -7 day) and date_add('2019-10-21', interval +1 day) and month(dt_ini) = '10' group by day(dt_ini) order by dt_ini ASC;

The dates are not fixed as is there. Each day looks back 7 days. So far the result is this:

2019-10-14 04:31:00
2019-10-15 06:55:00
2019-10-16 07:42:00
2019-10-17 07:06:00
2019-10-18 06:41:00
2019-10-19 06:13:00
2019-10-21 08:20:00

You may notice that on the 20th he has no event 99 so he did not bring in the consultation. Then this result with the improvement in select should no longer be presented as it is no longer 7 consecutive days with event 99.

Edit: I need results only to be presented when I have 7 consecutive days with event 99.

You can help me. Thanks in advance.

mysql database
  • 1

    I don’t quite understand what you want. Could improve the explanation!?

  • @Rbz made an Edit: I need results only to be presented when I have 7 consecutive days with event 99. In this example that I have done so far is missing the day 20 so should not be presented.

  • I believe your selection is wrong, if I understand correctly. To improve your question, show an example of the values you have in the BD before select, the values after select, and the values you want to have. To get better, create an example on dbfiddle .

1 answer

0


I managed to solve it. With the select below I can bring 7 consecutive days if all 7 have the event 99.

select if(COUNT(a.cd_ocorrencia) = 7, TRUE, FALSE) as ocorrencia FROM( 
select cd_ocorrencia, dt_ini 
from ocorrencias WHERE cd_motorista = '20427' AND cd_status = 99 
AND dt_ini BETWEEN DATE_ADD('2019-10-06', INTERVAL -6 DAY) AND DATE_ADD('2019-10-06', INTERVAL +1 DAY) 
/*AND MONTH(dt_ini) = '10' */
GROUP BY DAY(dt_ini) 
) a;

Browser other questions tagged mysql database

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