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

Question

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

Navigation

#1 by (1 votes)

1

Good morning, I need to return the sequence of the week of the month (of the reported period) in two fields, start date and end date. In PHP and MySQL . Currently returns the number of the week in the year, follow print for a 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:

mysql sql

asked by anonymous 17.05.2018 / 14:59

1 answer

How to compare part of strings in C? Pulse Effect does not work correctly when it has 2 elements

score 1 · Accepted Answer

Add the sequencia_semana field 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 this calculated value, thus having the week within the month.

See working in SQL Fiddle .