From: Andreas Joseph Krogh <andreak@officenet.no>
Organization: OfficeNet AS
To: pgsql-sql@postgresql.org
Subject: Problem with query on history table
Date: Mon, 27 Feb 2006 08:19:30 +0100
User-Agent: KMail/1.8.2
MIME-Version: 1.0
Content-Disposition: inline
Message-Id: <200602270819.30702.andreak@officenet.no>
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit

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,
    modified timestamp without time zone
);

CREATE TABLE hist (
    id serial NOT NULL,
    curr_id integer NOT NULL REFERENCES curr(id),
    etc integer NOT NULL,
    modified timestamp 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-26 00: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:00
  3 |       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-01 00: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-01 00: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>