sql query help

[kirk112]

I have the following query which selects all the invoice details from the database

[code]

SELECT cp.placement_id, cp.location, cp.split, cp.per_split, cp.fee, cp.value_to_con, cp.value_to_hh, cp.starting_salary, date_format(cp.start_date, '%M - %Y') as start_date, cp.financial_week, CONCAT( uc.firstname, ' ', uc.lastname ) AS con_name, CONCAT( uh.firstname, ' ', uh.lastname ) AS hh_name, c.company_name, s.source, CONCAT( can.first_name, ' ', can.last_name ) AS candidate_name, jt.job_title, cp.candidate_ref, date_format(cp.start_date, '%d/%m/%y') AS start_date_for, cp.invoice_number, cp.filestore_id, cf.file_name, cp.invoice_cancelled, date_format(cp.date_sent, '%d-%m-%y') AS date_sent, date_format(cp.date_paid, '%d-%m-%y') AS date_paid, date_format(cp.date_sent, '%M - %Y') AS date_sent_for

FROM candidates_placements AS cp
LEFT JOIN job_titles AS jt ON cp.job_title = jt.job_id
LEFT JOIN users AS uc ON cp.consultant = uc.user_id
LEFT JOIN users AS uh ON cp.headhunter = uh.user_id
LEFT JOIN clients AS c ON cp.company_id = c.client_ref
LEFT JOIN source AS s ON cp.source = s.source_id
LEFT JOIN candidates AS can ON cp.candidate_ref = can.candidate_ref
LEFT JOIN candidates_filestore AS cf ON cp.filestore_id = cf.file_id
WHERE invoice_paid = '0'
AND invoice_cancelled = '0'
AND date_sent <  date(date_sent, -INTERVAL clients.payment_terms DAYS) [/code]

[color=red]AND date_sent <  date(date_sent, -INTERVAL clients.payment_terms DAYS) [/color] I know this is totally wrong cant think how to create the query

In the clients table I have a payment_terms field, what need to do is be able to select all the information where the date_sent is not within the period that the client has to paid  ???

[kirk112]

I have changed the sql to

[code]
SELECT cp.placement_id, cp.location, cp.split, cp.per_split, cp.fee, cp.value_to_con, cp.value_to_hh, cp.starting_salary, date_format(cp.start_date, '%M - %Y') as start_date, cp.financial_week, CONCAT( uc.firstname, ' ', uc.lastname ) AS con_name, CONCAT( uh.firstname, ' ', uh.lastname ) AS hh_name, c.company_name, s.source, CONCAT( can.first_name, ' ', can.last_name ) AS candidate_name, jt.job_title, cp.candidate_ref, date_format(cp.start_date, '%d/%m/%y') AS start_date_for, cp.invoice_number, cp.filestore_id, cf.file_name, cp.invoice_cancelled, date_format(cp.date_sent, '%d-%m-%y') AS date_sent, date_format(cp.date_paid, '%d-%m-%y') AS date_paid, date_format(cp.date_sent, '%M - %Y') AS date_sent_for

FROM candidates_placements AS cp
LEFT JOIN job_titles AS jt ON cp.job_title = jt.job_id
LEFT JOIN users AS uc ON cp.consultant = uc.user_id
LEFT JOIN users AS uh ON cp.headhunter = uh.user_id
LEFT JOIN clients AS c ON cp.company_id = c.client_ref
LEFT JOIN source AS s ON cp.source = s.source_id
LEFT JOIN candidates AS can ON cp.candidate_ref = can.candidate_ref
LEFT JOIN candidates_filestore AS cf ON cp.filestore_id = cf.file_id
WHERE (invoice_paid = '0'
AND invoice_cancelled = '0'
AND date_sent NOT BETWEEN ADDDATE(NOW(), INTERVAL -20 DAY) AND NOW()
AND date_sent != '0000-00-00'
)
[/code]

And it does select the right values,  but if I chage -20 to c.payment_terms it brings in all the results.  Is it possible to use a value from the query before the query have run?  The c.payment_terms is different for every client

[kirk112]

Is it possible to use a value from the query before the query have run?  The c.payment_terms is different for every client

[kirk112]

*bump

[sasa]

is it possible?
date_sent < date_sent - same days

[kirk112]

thanks for the reply

but I  dont quite undrstand your answer,  I have got the query to work with a constant value ie -20 days but I want to change this to a variable depending on each row

[sasa]

is it what you want
[code] .... AND date_sent < DATE_SUB( CURDATE(), INTERVAL clients.payment_terms DAY)[/code]
you try to compare date_sent with (date_sent - 20 days). It is newer thrue