CODEWITHSUNDEEP
mysqlsql
edbird88
edbird88

Reputation: 128

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.

Upvotes: 4

Views: 87

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

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;

Upvotes: 3

Related Questions