How to return the sequence of the week in the reported period?

Question

Good morning, I need to return the sequence of the week of the month (of the reported period) in two fields, initial and final date. In PHP and MySQL. Currently returns the week number in the year, follow print for better understanding.

SQL code:

SELECT YEAR(dadf331.datlan)      AS ano, 
       MONTHNAME(dadf331.datlan) AS mes, 
       WEEK(dadf331.datlan)      AS semana, 
       SUM(totped)               AS totalv 
  FROM dadf331 
 WHERE tipped = 0 
   AND datlan >= '2017-09-01' 
   AND datlan <= '2017-09-30' 
 GROUP BY semana 
 ORDER BY semana ASC 

Expected result:

Answer 1

Add the field sequencia_semana as follows:

SELECT YEAR(dadf331.datlan)                                                                                AS ano,
       MONTHNAME(dadf331.datlan)                                                                           AS mes,
       WEEK(dadf331.datlan)                                                                                AS semana,
       SUM(totped)                                                                                         AS totalv,
       (WEEK(dadf331.datlan) + 1) - WEEK(DATE_ADD(dadf331.datlan, INTERVAL - DAY(dadf331.datlan) + 1 DAY)) AS sequencia_semana
  FROM dadf331
 WHERE tipped = 0
   AND datlan >= '2017-09-01'
   AND datlan <= '2017-09-30'
 GROUP BY semana
 ORDER BY semana ASC

This way you calculate the week of the first day of the month and subtract the week from the date of that calculated value, thus having the week within the month.

See working on SQL Fiddle.