cc5RZiee

1SELECT DATE_FORMAT(dt_validade, '%y-%m') as anoMes, count(*) as qtde
2FROM tabela
3WHERE dt_validade > '{$today}'
4GROUP BY anoMes
5ORDER BY anoMes
6	
7anoMes  | qtde
814-08   | 2
914-09   | 5
1014-12   | 10
1114-12   | 10
1214-12   | 10
1315-01   | 1
1415-03   | 1
15	
16$cols = array(
17    array(
18        'Período', 
19        'Quantidade'
20    )
21);
22$rows = array();
23
24$query = "A query passada";    
25$result = $query->result();
26foreach ($result as $r) {
27    array_push($rows, array(
28        $r->mesAno, 
29        (int) $r->qtde
30    ));
31}    
32
33$dados = array_merge($cols, $rows);     
34
35return json_encode($dados);
36	
37anoMes  | qtde
3814-08   | 2
3914-09   | 5
4014-10   | 0
4114-11   | 0
4214-12   | 10
4315-01   | 1
4415-03   | 1
45	
46$rows = array();
47
48$query = "SELECT DATE_FORMAT(dt_validade, '%m-%y') as anoMes, count(*) as qtde
49          FROM tabela
50          WHERE dt_validade > now()
51          GROUP BY anoMes
52          ORDER BY dt_validade";    
53
54$result = $query->result();
55foreach ($result as $r) {
56    $rows[$r->mesAno] = (int) $r->qtde;
57}    
58
59reset($rows);
60list($mes, $ano) = explode('-', key($rows));
61$current = strtotime(($ano+2000).'-'.$mes.'-01');
62
63end($rows);
64list($mes, $ano) = explode('-', key($rows));
65$last = strtotime(($ano+2000).'-'.$mes.'-01');
66
67$dates = array();
68while( $current <= $last ) { 
69    $dates[date('m-y', $current)] = 0;
70    $current = strtotime('+1 month', $current);
71}
72
73$rows = array_merge($dates, $rows);
74$dados = array( array('Período', 'Quantidade'));
75foreach($rows as $key => $value) $dados[] = array($key, $value);
76
77return json_encode($dados);
78	
79SELECT @mindate = MIN(dt_validade), @maxdate = MAX(dt_validade) FROM tabela
80	
81|                    @mindate |                     @maxdate |
82|-----------------------------|------------------------------|
83| July, 30 2014 00:00:00+0000 | March, 15 2015 00:00:00+0000 |
84	
85CREATE TEMPORARY TABLE 
86IF NOT EXISTS 
87ListaAnoMes AS
88SELECT Date_format(m1, '%y-%m') AS anoMes 
89FROM   (SELECT ( @mindate - INTERVAL Dayofmonth(@mindate)-1 day ) + 
90               INTERVAL m month AS 
91                      m1 
92        FROM   (SELECT @rownum := @rownum + 1 AS m 
93                FROM   (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1, 
94                       (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2, 
95                       (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3, 
96                       (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t4, 
97                       (SELECT @rownum :=- 1) t0) d1) d2 
98WHERE  m1 <= @maxdate 
99ORDER  BY m1;
100	
101SELECT * FROM LISTAANOMES
102
103| ANOMES |
104|--------|
105|  07-14 |
106|  08-14 |
107|  09-14 |
108|  10-14 |
109|  11-14 |
110|  12-14 |
111|  01-15 |
112|  02-15 |
113|  03-15 |
114	
115SELECT         B.AnoMes,
116               Count(a.id) as qtde
117FROM           ListaAnoMes as B
118LEFT JOIN      tabela as A 
119           ON  DATE_FORMAT(A.dt_validade, '%m-%y') = B.anoMes
120GROUP BY       B.AnoMes
121	
122| ANOMES | QTDE |
123|--------|------|
124|  01-15 |    1 |
125|  02-15 |    0 |
126|  03-15 |    1 |
127|  07-14 |    1 |
128|  08-14 |    2 |
129|  09-14 |    5 |
130|  10-14 |    0 |
131|  11-14 |    0 |
132|  12-14 |   10 |
133	
134SELECT DATE_FORMAT(dt_validade, '%m-%y') as mesAno, count(*) as qtde
135FROM tabela
136WHERE dt_validade > '{$today}' OR dt_validade = NULL OR dt_validade = ''
137GROUP BY mesAno