Counting weeks between two dates

Question

I am trying to perform a query between two dates that returns the number of the week and counts progressively the week.

As an example :

Stayed like this :

SELECT ROUND((DATEDIFF(max(atendido_em_data),min(atendido_em_data)))/7) as semana,min(atendido_em_data) as min,max(atendido_em_data) as max
from ger_posicao;

I don’t understand why the bank keeps returning me like this:

Answer 1

It would be the function below? (published in the SO in English)

SELECT FLOOR(DATEDIFF('2018-05-20', '2018-04-30'))/7;

To get the rounded value, as requested in the comments:

SELECT ROUND((DATEDIFF('2018-05-20', '2018-04-30'))/7);

Answer 2

When you say progressively count the weeks can mean many things.

However, in the example you present in the question I did not find what the meaning is, because week 1 has 3 days, and the days of the week (mon, ter, qua) do not correspond to the dates in reality (day 30/04 was a Monday rather than Sunday as the example suggests).

Anyway, O Mysql has date functions that you can use to solve this problem.

In this implementation, I used the Week(date, mode) and passed a 'base date' to control the count sequence:

SELECT DAYNAME(data), 
       data, 
       CONCAT('semana ', WEEK(data,1) - WEEK('20180428',1) + 1) AS semana
FROM tabela

See this example working on SQL Fiddle.

I hope I’ve helped.