Joining based on date (or date range)

I'm not sure if I've completely fudged the design of my small hobby database (I'm not a DBA by any means), but I have a table like this (primary key is (staffid, effectivefrom)):

staffid | target | effectivefrom
 --------|--------|---------------
  1      |  6.0   | 2012-01-01
  2      |  6.0   | 2012-01-01
  3      |  6.0   | 2012-01-01
  1      |  7.0   | 2012-03-01

So basically, three staff all start out with a target of 6.0, but on March, staff with ID 1 has a new target of 7.0. I want to maintain historical targets because it is relevant to other data in other tables.

I would like to have a user-defined function that takes a date as a parameter, and this function needs to join the above table with another table based on the date. Say the function is called with 1st of February as the date, I would like the result of the join to include the target column showing 6.0 for all staff.

Something like this (I think this won't work because there could be multiple rows before dateParameter):

SELECT othertable.*, targets.target
     FROM othertable
     JOIN targets ON
         othertable.staffid = targets.staffid AND
         targets.effectivedate <= dateParameter;

Please let me know if I have done an absolute DBA 'no-no' or whether I just need some caffeine.

You need to select the most recent record that's before or on your date of interest. A query similar to the following would do this.

declare @date datetime
select @date = '2012-02-01'

select t.StaffID
      ,t.Target
  from Targets t
  join (select StaffID
              ,max (EffectiveFrom)    as EffectiveFrom
          from targets
         where EffectiveFrom <= @date  -- Most recent record before this date
         group by StaffID) ct 
    on ct.StaffID = t.StaffID
   and ct.EffectiveFrom = t.EffectiveFrom

Unfortunately I don't have a running instance of PostgreSQL to hand, so this was on SQL Server, but the same basic structure should work on PostgreSQL.

StackExchange Database Administrators Q#14154, answer score: 5