I am using a query to get data about temp job & temp rates for an employee database. Problem is this query pulls up two records with different rates for the same work period. THE JOB_RATE table has two different job rates with different JOBRATE_EFFECTIVE_RATE . What candition should I add in this query so that it pulls up the Jobrate applicable to that particular WORKDATE & not all JOBRATES .

i.e,
say if Jobrate = 10 on 1-Dec-2002 & later revised to Jobrate =20 effective 1-Jan-2003, then for a particular workdate 16-Dec-02 ,
the report should display one record with Temp_rate= 10 instadof two records with diffenrent rates, other data being same

select EMPLOYEE.EMP_ID,
Job.Job_name Temp_job,
Job_Rate.Jobrate_Rate Temp_Rate,
To_Char(Work_Detail.Wrkd_Work_Date,'MM-DD-YYYY') WorkDate ,
To_Char(Work_Detail.wrkd_Start_Time,'HH24:MI') BeginTime ,
To_Char(Work_Detail.wrkd_End_time,'HH24:MI') EndTime
from Employee , Job, Job_Rate ,Work_Detail,Work_Summary
where EMPLOYEE.EMP_ID = WORK_SUMMARY.EMP_ID
AND WORK_SUMMARY.WRKS_ID = WORK_DETAIL.WRKS_ID
AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
AND JOB.JOB_ID = Job_Rate.Job_Id

You need to say:

AND job_rate.effective_date =
( SELECT MAX(jr.effective_date)
FROM job_rate jr
WHERE jr.effective_date <= Work_Detail.Wrkd_Work_Date
AND jr.job_id = job.job_id)

It is common to have a job_rate.end_date column to overcome this, so that the condition is simply:

AND Work_Detail.Wrkd_Work_Date BETWEEN job_rate.effective_date AND job_rate.end_date

This simplifies the query, but adds complication to the rate maintenance functionality.

Thanks Andrewst .

The query has worked & I am satisfied after testing it .Thanks a lot for the help.