Re: Linking two tables with Dates

From: Johannes Wahl <johannes.wahl_at_gnc.at>
Date: Tue, 20 Feb 2001 18:44:04 +0100
Message-ID: <3A92ACE3.AD9EC61C@gnc.at>

this should work:

SQL>
  1 SELECT

  2      Work.Work_Date,
  3      Work.Description,
  4      SUBSTR(
  5        MAX(TO_CHAR(Payrate.Datset,'YYYYMMDD') || Payrate.Rate_Hour),
  6      9) Work_Payrate
  7  FROM
  8      Work,
  9      Payrate
 10  WHERE
 11      Payrate.Datset <= Work.Work_Date
 12  GROUP BY
 13      Work.Work_Date,
 14*     Work.Description

WORK_DATE DESCRIPTION          WORK_PAYRATE
--------- -------------------- --------------------
12-FEB-01 worked on something  21.45
14-FEB-01 worked on something  21.45

15-FEB-01 worked on something 21.45
20-FEB-01 worked on something 23.45

note: "Work_Payrate" comes here in VARCHAR2 format. if you need NUMBER, add the function TO_NUMBER().

Zanfar Ali wrote:

> I have two tables called work and payrate:
>
> in work there is two fields a date and a description.
>
> WORK
> DATE DESCRIPTION
> 20-Feb-2001 worked on sometething
> 15-Feb-2001 worked on sometething
> 14-Feb-2001 worked on sometething
> 12-Feb-2001 worked on sometething
>
> in payrate i have a history of a persons payrates and the date it was set
>
> PAYRATE
> DATSET RATE/Hour
> 16-Feb-2001 23.45
> 12-Feb-2001 21.45
>
> the dateset means that from 12-Feb-2001 to 15-Feb-2001 the payrate was 21.45
> and then from 16-Feb-2001 to today the payrate was 23.45.This table can have
> more entries.
>
> What i need to do is to link the work table entries with the corresponding
> payrate...?
>
> Any help will be highly appreciated!
Received on Tue Feb 20 2001 - 11:44:04 CST