Jump to content


Photo

sql query help


  • Please log in to reply
6 replies to this topic

#1 kirk112

kirk112
  • Members
  • PipPipPip
  • Advanced Member
  • 98 posts
  • LocationEngland

Posted 25 October 2006 - 11:09 AM

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


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)

AND date_sent <  date(date_sent, -INTERVAL clients.payment_terms DAYS) 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  ???


#2 kirk112

kirk112
  • Members
  • PipPipPip
  • Advanced Member
  • 98 posts
  • LocationEngland

Posted 25 October 2006 - 12:33 PM

I have changed the sql to

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'
 )

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


#3 kirk112

kirk112
  • Members
  • PipPipPip
  • Advanced Member
  • 98 posts
  • LocationEngland

Posted 25 October 2006 - 03:25 PM

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


#4 kirk112

kirk112
  • Members
  • PipPipPip
  • Advanced Member
  • 98 posts
  • LocationEngland

Posted 25 October 2006 - 09:41 PM

*bump

#5 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 25 October 2006 - 10:04 PM

is it possible?
date_sent < date_sent - same days

#6 kirk112

kirk112
  • Members
  • PipPipPip
  • Advanced Member
  • 98 posts
  • LocationEngland

Posted 25 October 2006 - 10:13 PM

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



#7 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 25 October 2006 - 10:51 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users