Adding Days To A Date In A Where Clause

[NiallFH]

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() ?

Edited December 19, 2012 by NiallFH

[Barand]

... AND N.invoice_date + INTERVAL 30 DAY < CURDATE()

[NiallFH]

Absolutely fantastic Barand, thank you very much!