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.


That is going to be horribly messy, since you'll need to find the single (staffid, effectivefrom) pair that's the newest prior to the dateParameter.

What I'd do is add another field effectiveTo to go with effectiveFrom, and then when you add a new target for a staff member, before you add it check and see if there are any current targets (effectiveTo IS NULL) and if there are then set their end date to the new target start date (optionally: throw an error if there's more than one current target).

So, the table turns out looking like:

 staffID | target | effectiveFrom | effectiveTo
  1      |  6.0   | 2012-01-01    | 2012-03-01
  2      |  6.0   | 2012-01-01    | NULL
  3      |  6.0   | 2012-01-01    | NULL
  1      |  7.0   | 2012-03-01    | NULL

That makes your search query look like this:

SELECT  othertable.*, targets.target
FROM    othertable
            INNER JOIN 
        targets ON othertable.staffid = targets.staffid
WHERE dateParameter > effectiveFrom AND (dateParameter <= effectiveTo OR effectiveTo IS NULL)

The potential downside of that is data inconsistency (i.e. staff members having overlapping targets) - you can fairly easily write a script to check that though. I'd recommend making an addTarget(staffID, target, fromDate) stored procedure and wrapping all the checking/updating logic in that, also.

(A couple of style notes - I rewrote your query slightly to seperate the join condition from the where clause. Also, I know it's only an example, but SELECT table.* is a bad habit to get into - you land up transferring far more data than you need.)

| improve this answer | |
  • Thanks for the info, this looks like an easier approach than what I have. PostgreSQL allows trigger functions to execute prior to an INSERT or UPDATE, so I can just write a trigger function to ensure no date regions overlap. Also, I know that SELECT table.* is bad, but in this case it was purely just because it was an example, I would never do it outside of a test/example query. – dreamlax Feb 29 '12 at 21:43
  • If you want that BETWEEN to work, you'll have to replace NULL with the maximum allowed date, ('9999-12-31' or whatever is allowed in Postgres). – ypercubeᵀᴹ Feb 29 '12 at 22:09
  • Good catch. Altered my query slightly. (I went for coping with NULL rather than replacing the date because it seems a little less likely to break that way.) – Simon Righarts Feb 29 '12 at 22:30
  • This ultimately looks like the cleaner way to go, the join looks less intimidating than the other answer, even though the other answer is more direct (i.e. works with what I have currently). Just one question, why is it important to separate the join condition from the where clause? Does this help with performance? (noob here) – dreamlax Mar 1 '12 at 0:43
  • It's just a style note - it looks cleaner (future maintainability etc). Performance-wise I don't think it matters. – Simon Righarts Mar 1 '12 at 1:05

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
  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.

| improve this answer | |
  • This approach works with what I have currently, I managed to "port" it to PostgreSQL and it works well, but I think the other answer is probably a better approach, I think my table design is/was broken. – dreamlax Mar 1 '12 at 0:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.