<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-15"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Ciao a tutti,<br>
ho una tabella con questa struttura:<br>
<br>
id // contatore<br>
data // datetime attivazione<br>
data_chiuso // datetime chiusura<br>
stato // intero, 0=aperto, 1=assegnato, 2=chiuso<br>
<br>
Vorrei fare una query che mi ritorna per ogni giorno quanti ticket sono
stati assegnati e quanti chiusi, ovvero una cosa del tipo<br>
<br>
<table id="table_results" class="data">
<tbody>
<tr class="odd">
<td class="nowrap">data</td>
<td class="nowrap" align="right">assegnati</td>
<td class="nowrap" align="right">chiusi</td>
</tr>
<tr class="even">
<td class="nowrap">2007-05-16</td>
<td class="nowrap" align="right">5</td>
<td class="" align="right"><i>0</i></td>
</tr>
<tr class="odd">
<td class="nowrap">2007-05-21</td>
<td class="nowrap" align="right">1</td>
<td class="" align="right"><i>0</i></td>
</tr>
<tr class="even">
<td class="nowrap">2007-05-22</td>
<td class="nowrap" align="right">1</td>
<td class="" align="right"><i>0</i></td>
</tr>
<tr class="odd">
<td class="nowrap">2007-05-23</td>
<td class="nowrap" align="right">1</td>
<td class="" align="right"><i>0</i></td>
</tr>
<tr class="even">
<td class="nowrap">2007-05-25</td>
<td class="nowrap" align="right">8</td>
<td class="nowrap" align="right">3</td>
</tr>
<tr class="odd">
<td class="nowrap">2007-05-26</td>
<td class="nowrap" align="right">4</td>
<td class="nowrap" align="right">9</td>
</tr>
<tr class="even">
<td class="nowrap">2007-05-27</td>
<td class="nowrap" align="right">3</td>
<td class="nowrap" align="right">1</td>
</tr>
<tr class="odd">
<td class="nowrap">2007-05-28</td>
<td class="nowrap" align="right">5</td>
<td class="nowrap" align="right">3</td>
</tr>
<tr class="even">
<td class="nowrap">2007-05-29</td>
<td class="nowrap" align="right">15</td>
<td class="nowrap" align="right">5</td>
</tr>
<tr class="odd">
<td class="nowrap">2007-05-30</td>
<td class="nowrap" align="right">2</td>
<td class="" align="right"><i>0</i></td>
</tr>
</tbody>
</table>
<br>
<br>
Ho risolto in questo modo:<br>
<br>
SELECT DATE(c1.data),q1 as assegnati,q2 as chiusi from <br>
<br>
((SELECT stato,data,COUNT(id) as q1 FROM codici <br>
WHERE stato=1 <br>
GROUP BY DATE(data)) as c1) <br>
<br>
LEFT JOIN<br>
<br>
((SELECT stato,data,count(id) AS q2 FROM codici <br>
WHERE stato=2 <br>
GROUP BY DATE(data)) as c2) <br>
<br>
ON DATE(c1.data)=DATE(c2.data) <br>
GROUP BY DATE(c1.data)<br>
<br>
Funziona bene, ma c'è un modo migliore per farlo?<br>
<br>
Ciao<br>
Marcello<br>
<br>
<br>
</body>
</html>