1 Reply Latest reply: May 16, 2013 1:26 AM by jagstirling RSS

    Analysing By Date Range

      I'm new to QV, looking for some advice on the following;

       

      I have 2 tables containing a RATE per hour and the HOURS worked, linked by a common ID field.

       

      TABLE 1
      IDSTART_DATE
      END_DATERATE
      901-JAN-1304-JAN-13$50
      905-JAN-1331-JAN-13$55

       

      TABLE 2
      IDDATEHOURS
      912-JAN-134

       

      How can I select the appropriate RATE in TABLE 1 to match the HOURS worked in TABLE 2 on a certain date to give me a value ?

       

      In the example, employee 9 worked 4 HOURS on the 12-JAN-13 and this equates to a RATE of $55, (as 12-JAN-13 falls between the START_DATE 05-JAN-13 and END_DATE 31-JAN-13) which is $220 (4 HOURS * $%% per hour).

       

      Any advice welcome.