how to group invoices by date in 2-month periods

[peppericious]

I've written a script to help me keep track of VAT on invoices. It all works fine. However, I would like to group invoices in 2-month periods.

 

How can I query the invoices table such that records are returned in 2-month periods according to the inv_date?

 

In other words, I want to be able to display a table like this, grouped Jan/Feb, Mar/Apr, etc, with VAT and Inv totals...

 

Jan/Feb

---------------------------------------------------------------

Inv. no. | Desc. | Inv. Date | Vat Amount | Inv. Total

xxx | xxxx | 22-01-2013 | 23.82 | 127.37

xxx | xxxx | 28-01-2013 | 14.81 | 79.21

xxx | xxxx | 14-02-2013 | 20.96 | 112.08

---------------------------------------------------------------

59.59 318.66

 

Mar/Apr

 

---------------------------------------------------------------

Inv. no. | Desc. | Inv. Date | Vat Amount | Inv. Total

xxx | xxxx | 17-03-2013 | 19.97 | 106.77

xxx | xxxx | 18-03-2013 | 20.69 | 110.62

xxx | xxxx | 14-04-2013 | 2.80 | 14.99

---------------------------------------------------------------

43.46 232.38

 

May/June

 

---------------------------------------------------------------

Inv. no. | Desc. | Inv. Date | Vat Amount | Inv. Total

xxx | xxxx | .......... | ..... | .....

xxx | xxxx | .......... | ..... | .....

---------------------------------------------------------------

--.-- ---.00

 

 

 

Any help will be much appreciated.

[requinix]

How are you building the report now? I mean in terms of code and SQL.

[peppericious]

How are you building the report now? I mean in terms of code and SQL.

 

.. er, I'm not building any report yet because I don't know how. I'm simply retrieving all records, sorted by inv_date, as follows:

SELECT inv_number, vat, total, inv_date FROM `invoices` order by inv_date;

Edited February 1, 2013 by peppericious

[peppericious]

How are you building the report now? I mean in terms of code and SQL.

 

Ok, so far I've just got this... a chronological list of invoices. As I say, though, I'd like to group them in 2-month periods...

<?php
include('includes/mysqli_connect.php');
$q = "SELECT company, description, vat, (total-vat) as subtotal, total, inv_date FROM purchase ORDER BY inv_date";
$r = mysqli_query($dbc, $q);
$retrieved = mysqli_num_rows($r);
 if ($retrieved > 0) {
$records_output = '<table width="100%" cellpadding="2">
  <tr>
      <td>Company</td>
      <td>Invoice Date</td>
      <td>Vat</td>
      <td>Subtotal</td>
      <td>Total</td>
      <td>Inv Date</td>
  </tr>
';
while ($row = mysqli_fetch_array($r)) {
 $company        = $row['company'];
 $description    = $row['description'];
 $vat            = $row['vat'];
 $subtotal       = $row['subtotal'];
 $total          = $row['total'];
 $inv_date       = $row['inv_date'];
 $records_output .= "<tr>
       <td>$company</td>
       <td>$description</td>
       <td>$vat</td>
       <td>$subtotal</td>
       <td>$total</td>
       <td>$inv_date</td>
   </tr>";
}
$records_output .= '</table';
}
echo $records_output;

[requinix]

One question: is there a chance that there might not be invoices for a month pair? If so then the code is a fair bit more complicated.

[peppericious]

One question: is there a chance that there might not be invoices for a month pair? If so then the code is a fair bit more complicated.

 

No, there will always be invoices for every month pair.

[requinix]

Right now you have logic like

if there are results {
start table
for each row {
	output row
}
end table
}

Refactor it into more of

if there are results {
grab a row

previous year = year from the row
previous month = month rounded down to an odd-numbered month
// jan->jan, feb->jan, mar->mar, apr->mar, etc.
// use a month number, not a name

start table for this year and the couple months

do {
	output row

	grab the next row
	current year = year from the new row
	current month = month rounded down to an odd-numbered month

	if the current year and month do not match the previous year and month {
		end table
		start table for the current year and the couple months
		update previous year and month with the current year and month
	}
}

end table
}

Edited February 1, 2013 by requinix

[peppericious]

Thanks for your help with this, which I'm working to try to implement.

 

In the meantime, I've created a little form with 'from' and 'to' datepicker inputs. According to the dates entered in those, I have an sql query to retrieve the appropriate records for whatever time period I wish.

 

$q = "SELECT company, description, vat, (total-vat) as subtotal, total, MONTHNAME(inv_date) as month, DAY(inv_date) as day FROM purchase
WHERE inv_date >= '$from' AND inv_date <= '$to' ORDER BY inv_date";

Edited February 2, 2013 by peppericious