List all months in an interval, or query or schedule?

Question

List all months in an interval, or query or schedule?

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)

3

I'm making a chart that lists a certain amount per month, but you can have more that has nothing, so it does not return any value and does not list it on the chart.

I do not know if I should change the query or programming, can anyone help me?

My query looks like this:

SELECT DATE_FORMAT(dt_validade, '%y-%m') as anoMes, count(*) as qtde
FROM tabela
WHERE dt_validade > '{$today}'
GROUP BY anoMes
ORDER BY anoMes

Her return is more or less that

anoMes  | qtde
14-08   | 2
14-09   | 5
14-12   | 10
14-12   | 10
14-12   | 10
15-01   | 1
15-03   | 1

And with that, in php I wrap a loop in the result, I play the data for an array that sends a json_encode to the data for the chart, but with that it will only show me in the data graph for months 08, 09 and 12 , but I need to also show the months 10 and 11 with zero values.

And in short, what I did in programming was this:

$cols = array(
    array(
        'Período', 
        'Quantidade'
    )
);
$rows = array();

$query = "A query passada";    
$result = $query->result();
foreach ($result as $r) {
    array_push($rows, array(
        $r->mesAno, 
        (int) $r->qtde
    ));
}    

$dados = array_merge($cols, $rows);     

return json_encode($dados);

And as a suggestion given to me, the expected result is this:

anoMes  | qtde
14-08   | 2
14-09   | 5
14-10   | 0
14-11   | 0
14-12   | 10
15-01   | 1
15-03   | 1

Link to the build in SQLFIDDLE , so I guess I can get a little clearer.

php sql

asked by anonymous 12.08.2014 / 15:50

3 answers

Dynamic Pointer Allocation in C ++ Release external access on sql server 2014

score 2 · Answer 1

It was not the most beautiful thing in the world, but it worked:

$rows = array();

$query = "SELECT DATE_FORMAT(dt_validade, '%m-%y') as anoMes, count(*) as qtde
          FROM tabela
          WHERE dt_validade > now()
          GROUP BY anoMes
          ORDER BY dt_validade";    

$result = $query->result();
foreach ($result as $r) {
    $rows[$r->mesAno] = (int) $r->qtde;
}    

reset($rows);
list($mes, $ano) = explode('-', key($rows));
$current = strtotime(($ano+2000).'-'.$mes.'-01');

end($rows);
list($mes, $ano) = explode('-', key($rows));
$last = strtotime(($ano+2000).'-'.$mes.'-01');

$dates = array();
while( $current <= $last ) { 
    $dates[date('m-y', $current)] = 0;
    $current = strtotime('+1 month', $current);
}

$rows = array_merge($dates, $rows);
$dados = array( array('Período', 'Quantidade'));
foreach($rows as $key => $value) $dados[] = array($key, $value);

return json_encode($dados);

score 1 · Answer 2

My knowledge of PHP is very limited, but I can try to help with a bank side solution.

Establish the period

SELECT @mindate = MIN(dt_validade), @maxdate = MAX(dt_validade) FROM tabela

Partial result:

|                    @mindate |                     @maxdate |
|-----------------------------|------------------------------|
| July, 30 2014 00:00:00+0000 | March, 15 2015 00:00:00+0000 |

Get the list of months between the two periods: Store in temporary table

CREATE TEMPORARY TABLE 
IF NOT EXISTS 
ListaAnoMes AS
SELECT Date_format(m1, '%y-%m') AS anoMes 
FROM   (SELECT ( @mindate - INTERVAL Dayofmonth(@mindate)-1 day ) + 
               INTERVAL m month AS 
                      m1 
        FROM   (SELECT @rownum := @rownum + 1 AS m 
                FROM   (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1, 
                       (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2, 
                       (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3, 
                       (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t4, 
                       (SELECT @rownum :=- 1) t0) d1) d2 
WHERE  m1 <= @maxdate 
ORDER  BY m1;

Partial result:

SELECT * FROM LISTAANOMES

| ANOMES |
|--------|
|  07-14 |
|  08-14 |
|  09-14 |
|  10-14 |
|  11-14 |
|  12-14 |
|  01-15 |
|  02-15 |
|  03-15 |

Cross the two tables

SELECT         B.AnoMes,
               Count(a.id) as qtde
FROM           ListaAnoMes as B
LEFT JOIN      tabela as A 
           ON  DATE_FORMAT(A.dt_validade, '%m-%y') = B.anoMes
GROUP BY       B.AnoMes

Result:

| ANOMES | QTDE |
|--------|------|
|  01-15 |    1 |
|  02-15 |    0 |
|  03-15 |    1 |
|  07-14 |    1 |
|  08-14 |    2 |
|  09-14 |    5 |
|  10-14 |    0 |
|  11-14 |    0 |
|  12-14 |   10 |

score 0 · Answer 3

Do you want a date range?

SELECT DATE_FORMAT(dt_validade, '%m-%y') as mesAno, count(*) as qtde
FROM tabela
WHERE dt_validade > '{$today}' OR dt_validade = NULL OR dt_validade = ''
GROUP BY mesAno

I do not know if the default of the null field or string is empty, depending on whether you need the 2 or, sṍ a