Problem with query on history table - Mailing list pgsql-sql

Hi all!

I don't know if there's a standard solution to the kind of problem I'm trying 
to solve, but I will appreciate your thougts(and maybe solution:) on this 
problem of mine:

I have 2 tables: hist and curr which hold numbers for "history-data" and 
"current-data" respectivly. Here is a simplified version of the schema:

CREATE TABLE curr (   id integer NOT NULL,   etc integer NOT NULL,   created timestamp without time zone NOT NULL,
modifiedtimestamp without time zone
 
);

CREATE TABLE hist (   id serial NOT NULL,   curr_id integer NOT NULL REFERENCES curr(id),   etc integer NOT NULL,
modifiedtimestamp without time zone NOT NULL
 
);

andreak=# SELECT * from curr;id | etc |       created       |      modified
----+-----+---------------------+--------------------- 1 |   5 | 2006-02-01 00:00:00 | 2 |  10 | 2006-01-15 00:00:00 |
2006-01-2600:00:00 3 |  10 | 2006-01-08 00:00:00 | 2006-01-25 00:00:00
 
(3 rows)

andreak=# SELECT * from hist;id | curr_id | etc |      modified
----+--------+-----+--------------------- 1 |       3 |  30 | 2006-01-16 00:00:00 2 |       3 |  20 | 2006-01-25
00:00:003 |       2 |  20 | 2006-01-26 00:00:00
 
(3 rows)

Now - I would like to get a report on what the "ETC" is on a given entry in 
"curr" in a given "point in time". Let me explain. If I want status for 17. 
jan.(17.01.2006) I would like to get these numbers out from the query:
id |       created       |    curr_modified    |    hist_modified    | etc
----+---------------------+---------------------+---------------------+----- 3 | 2006-01-08 00:00:00 | 2006-01-25
00:00:00| 2006-01-16 00:00:00 |  30 2 | 2006-01-15 00:00:00 | 2006-01-26 00:00:00 | 2006-01-26 00:00:00 |  20 1 |
2006-02-0100:00:00 |                     |                     |   5
 


That is; If the entry is modified after it's created, a snapshot of the "old 
version" is copied to table "hist" with the hist.modified field set to the 
"modified-timestamp". So there will exist several entries in "hist" for each 
time an entry in "curr" is modified.

If I want status for the 27. jan. I would like the query to return the 
following rows:
id |       created       |    curr_modified    |    hist_modified    | etc
----+---------------------+---------------------+---------------------+----- 3 | 2006-01-08 00:00:00 | 2006-01-25
00:00:00| 2006-01-25 00:00:00 |  10 2 | 2006-01-15 00:00:00 | 2006-01-26 00:00:00 | 2006-01-26 00:00:00 |  10 1 |
2006-02-0100:00:00 |                     |                     |   5
 

select curr.id, curr.created, curr.modified as curr_modified, hist.modified as 
hist_modified, coalesce(hist.etc, curr.etc) as etc FROM curr LEFT OUTER JOIN 
hist ON(curr.id = hist.curr_id) WHERE ...

I'm really stuck here. It seems to me that I need a lot of 
CASE...WHEN...ELSE.. statements in the query, but is there an easier way?

--
Andreas Joseph Krogh <andreak@officenet.no>