MySQL version 4.0
I have two tables, invoices (a table of invoices) and invworks (the individual work carried out on each of the invoices)
invoices
invoice_id int(11)
invoice_date date
invoice_terms int(3)
invoice_status int(1)
invworks
mworks_id int(11)
mworks_con int(11)
mworks_rate decimal(10,2)
mworks_qty decimal(10,3)
What I am trying to calculate in a query is the total sum of work completed that is OVERDUE.
In the invoices table, the field 'invoice_terms' is a figure of days that the invoiced customer must pay the invoice by after the issue date, usually anything up to 60. 'invoice_status' is an integer, 1, 2 or 3; '2' being the appropriate status in this instance, as that indicates that it has been invoiced.
In the invworks table, mworks_con corresponds with invoice_id to link the work to the invoice. mworks_rate is a decimal number showing the value of a unit of work and mworks_qty shows the quantity of those units to be completed in the overall work.
What I want this query to do is to show the complete sum of work completed for all invoices that are PAST the invoice date + days specified in the terms.
Therefore, I have now put together the following query:
echo"<p>Overdue: ";
$get_overdue_total = mysql_query("
SELECT
SUM(W.mworks_rate * W.mworks_qty) AS total
FROM val_invworks W, val_invoices N
WHERE
W.mworks_con = N.invoice_id AND
N.invoice_status = 2 AND
DATE_FORMAT(N.invoice_date,'%Y-%m-%d') + 30 days < CURDATE()
",$objConnect);
while($ovdata = mysql_fetch_array($get_overdue_total))
{
$total = number_format($ovdata['total'],2);
echo"<b>£$total</b></p>";
}
I have had a look at the mySQL manual and I am at a bit of a loss. Could anyone perhaps suggest the best way to write the WHERE clause, where I currently have DATE_FORMAT(N.invoice_date,'%Y-%m-%d') + 30 days < CURDATE() ?
