SQL select value if no corresponding value exists in another table

Question

I have a database which tries to acheive point-in-time information by having a master table and a history table which records when fields in the other table will/did change. e.g.

Table: Employee

Id  | Name      | Department
-----------------------------
0   | Alice     | 1
1   | Bob       | 1

Table: History

ChangeDate   | Field      | RowId  | NewValue    
---------------------------------------------
05/05/2009   | Department | 0      | 2

That records that employee 0 (Alice) will move to department 2 on 05/05/2009.

I want to write a query to determine the employee's department on a particular date. So it needs to:

• Find the first history record for that field and employee before given date
• If none exists then default to the value currently in the master employee table.

How can I do this? My intuition is to select the first row of a result set which has all suitable history records reverse ordered by date and with the value in the master table last (so it's only the first result if there are no suitable history records), but I don't have the required SQL-fu to achieve this.

---

Note: I am conscious that this may not be the best way to implement this system - I am not able to change this in the short term - though if you can suggest a better way to implement this I'd be glad to hear it.

Answer 1

SELECT  COALESCE (
        (
        SELECT  newValue
        FROM    history
        WHERE   field = 'Department'
                AND rowID = ID
                AND changeDate =
                (
                SELECT MAX(changedate)
                FROM   history
                WHERE  field = 'Department'
                       AND rowID = ID
                       AND changeDate <= '01/01/2009'
                )
        ), department)
FROM    employee
WHERE   id = @id

In both Oracle and MS SQL, you can also use this:

SELECT  COALESCE(newValue, department)
FROM    (
        SELECT  e.*, h.*,
                ROW_NUMBER() OVER (PARTITION BY e.id ORDER BY changeDate) AS rn
        FROM    employee e
        LEFT OUTER JOIN
                history h
        ON      field = 'Department'
                AND rowID = ID
                AND changeDate <= '01/01/2009'
        WHERE   e.id = @id
        )
WHERE rn = 1

Note, though, that ROWID is reserved word in Oracle, so you'll need to rename this column when porting.

Answer 2

This should work:

select iif(history.newvalue is null, employee.department, history.newvalue)
as Department
from employee left outer join history on history.RowId = employee.Id
and history.changedate < '2008-05-20'  // (i.e. given date)
and history.changedate = (select max(changedate) from history h1
where h1.RowId = history.RowId and h1.changedate <= history.changedate)