I need to list every month within a period, including the ones that did not move, for example:
My Query:
SELECT LEFT(B8.B8_DTVALID, 06) AS VALIDADE,
SUM(B8.B8_SALDO) AS VENCIDO,
0 AS VENCENDO,
0 AS AVENCER
FROM SB8030 B8
INNER JOIN SB1030 B1
ON B1.B1_COD = B8.B8_PRODUTO
AND B1.B1_GRUPO IN ('Q1', 'Q4', 'Q5')
AND B1.D_E_L_E_T_ = ' '
WHERE B8.B8_DTVALID BETWEEN 'dtIni' AND 'dtFim'
AND B8.B8_DTVALID < CONVERT(VARCHAR(10), GETDATE(), 112)
AND (B8_SALDO > 0 OR B8_EMPENHO > 0)
AND B8.D_E_L_E_T_ = ' '
GROUP BY LEFT(B8.B8_DTVALID, 06)
I use QUERY 3x repeated changing 'DTVALID' < ' or '=' or '&' CONVERT "with UNION to join the 3 queries
SQL return:
Mes/Ano | Vencidos | A Vencer | Vencendo
01/2015 | 5 | 0 | 0
02/2015 | 1 | 0 | 0
04/2015 | 15 | 0 | 0
06/2015 | 7 | 0 | 0
07/2015 | 1 | 200 | 3
09/2015 | 0 | 578 | 0
12/2015 | 0 | 231 | 0
Expected result
Mes/Ano | Vencidos | A Vencer | Vencendo
01/2015 | 5 | 0 | 0
02/2015 | 1 | 0 | 0
03/2015 | 0 | 0 | 0
04/2015 | 15 | 0 | 0
05/2015 | 0 | 0 | 0
06/2015 | 7 | 0 | 0
07/2015 | 1 | 200 | 3
08/2015 | 0 | 0 | 0
09/2015 | 0 | 578 | 0
10/2015 | 0 | 0 | 0
11/2015 | 0 | 0 | 0
12/2015 | 0 | 231 | 0
PHP code
$aDados = $rep->SelDados($dtIni, $dtFim);
$nCount = count($aDados);
for ($x = 0; $x < $nCount; $x++) {
$oDados = new Validade($aDados[$x]['VALIDADE'], $aDados[$x]['VENCIDO'], $aDados[$x]['VENCENDO'], $aDados[$x]['AVENCER']);
echo "<tr class=\"font01\">";
echo "<td>".$oDados->GetValidade()."</td>";
echo "<td>".$oDados->GetVencido()."</td>";
echo "<td>".$oDados->GetVencendo()."</td>";
echo "<td>".$oDados->GetAvencer()."</td>";
echo "</tr>";
}
Would anyone have any idea how to solve this for QUERY or PHP?