edbird88 edbird88 - 7 months ago 14
SQL Question

month starting another day

I've been using

MONTH()
function to get the months and grouping by month, like this example query.

SELECT
t1.ano,
t1.mes,
tempo_extra,
tempo_ativo,
tempo_extra / tempo_ativo AS volume_extra
FROM
(SELECT
YEAR(`data`) AS ano,
MONTH(`data`) AS mes,
SUM(tempo) AS tempo_extra
FROM
rh.aprovacoes
WHERE
(tipo = 'BH' OR tipo = 'HE')
AND estado = 1
AND YEAR(aprovacoes.`data`) = 2016
GROUP BY MONTH(`data`)) AS t1
LEFT JOIN
(SELECT
MONTH(`data`) AS mes, SUM(ativo) AS tempo_ativo
FROM
rh.processamento
GROUP BY MONTH(`data`)) AS t2 ON t1.mes = t2.mes
ORDER BY mes DESC;


How can i make months start on 23rd of the last month and end on 22nd of the current month.

For example, April starting on March 23rd and end on April 22nd.

Answer

Simply subtract 22 days from your date and add a month:

(`data` - interval 22 day) + interval 1 month
  • March 22 => February 28 or 29 => March 28 or 29
  • March 23 => March 1 => April 1
  • April 22 => March 31 => April 30
  • April 23 => April 1 => May 1

SQL fiddle: http://sqlfiddle.com/#!9/9eecb7d/54883

Your query joins records regardless of the year by the way. I don't think this is desired, so in below query I've corrected this.

SELECT t1.ano, t1.mes, tempo_extra, tempo_ativo, tempo_extra/tempo_ativo AS volume_extra
FROM 
(
  SELECT 
    YEAR(data - interval 22 day + interval 1 month) AS ano,
    MONTH(data - interval 22 day + interval 1 month) AS mes, 
    SUM(tempo) AS tempo_extra
  FROM rh.aprovacoes
  WHERE (tipo = 'BH' OR tipo = 'HE') 
  AND estado = 1 
  AND YEAR(aprovacoes.data - interval 22 day + interval 1 month) = 2016
  GROUP BY 
    YEAR(data - interval 22 day + interval 1 month), 
    MONTH(data - interval 22 day + interval 1 month)
) AS t1
LEFT JOIN 
(
  SELECT 
    YEAR(data - interval 22 day + interval 1 month) AS ano, 
    MONTH(data - interval 22 day + interval 1 month) AS mes, 
    SUM(ativo) AS tempo_ativo
  FROM rh.processamento
  GROUP BY 
    YEAR(data - interval 22 day + interval 1 month), 
    MONTH(data - interval 22 day + interval 1 month)
) AS t2 ON t1.ano = t2.ano AND t1.mes = t2.mes
ORDER BY t1.ano DESC, t1.mes DESC;
Comments