List every month in Query or PHP?

Question

I need to list every month within a period, including those that have not moved, example below:

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 repeated QUERY 3x by changing "DTVALID '<' or '=' or '>' CONVERT" with UNION to merge the 3 queries

Return of the SQL:

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 by QUERY or PHP ?

Answer 1

I don’t know if this helps, but a way to get every month through php - in a very elegant way - would be through the class DatePeriod.

Example:

$first = DateTime::createFromFormat('d/m', '01/01');

$interval = DateInterval::createFromDateString('1 month');

$last = DateTime::createFromFormat('d/m', '31/12');


$period = new DatePeriod($first, $interval, $last);


foreach ($period as $date) {

    echo $date->format('m/Y'), PHP_EOL;
}

Exit:

01/2015
02/2015
03/2015
04/2015
05/2015
06/2015
07/2015
08/2015
09/2015
10/2015
11/2015
12/2015 

Answer 2

You can put inside the date list this way:

$dataList = array();

$aDados = $rep->SelDados($dtIni, $dtFim);

$nCount = count($aDados);           

function getInterval($nCount) {
    $m = 1;
    for ($i=12; <=132; $i++ ) {
        if ($nCount <= $i && $nCount <= ($i+12)) {
           $interval = $m.' month' . ($m > 1) ? 's' : '';
        }
        $m++;
    }
    if ($nCount <=132) {
        $interval = '1 year';
    }
return  $interval;
}

$interval = getInterval();

$firstDate   = DateTime::createFromFormat('d/m', $dtIni);
$setInterval = DateInterval::createFromDateString($interval);
$lastDate    = DateTime::createFromFormat('d/m', $dtFim);

$datePeriod = new DatePeriod($firstDate, $setInterval, $lastDate);
$i=0;

foreach ($datePeriod as $date) {
 $oDados = new Validade($aDados[$i]['VALIDADE'], $aDados[$i]['VENCIDO'], $aDados[$i]['VENCENDO'], $aDados[$i]['AVENCER']);
   if ($oDados->GetValidade() == $date->format('m/Y')) {
       echo "<tr class=\"font01\">";
       echo "<td>".$date->format('m/Y')."</td>";
       echo "<td>".$oDados->GetVencido()."</td>";
       echo "<td>".$oDados->GetVencendo()."</td>";
       echo "<td>".$oDados->GetAvencer()."</td>";
       echo "</tr>";
   } else {
       echo "<tr class=\"font01\">";
       echo "<td>".$date->format('m/Y')."</td>";
       echo "<td>0</td>";
       echo "<td>0</td>";
       echo "<td>0</td>";
       echo "</tr>";
   }
$i++;
}