Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    New Delhi
    Posts
    11

    Unanswered: Newbie : Need Help in joining Multiple tables

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Newbie : Need Help in joining Multiple tables

    Originally posted by ritz1975
    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.

  3. #3
    Join Date
    Jan 2003
    Location
    New Delhi
    Posts
    11

    Re: Newbie : Need Help in joining Multiple tables

    Thanks Andrewst .

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •