From: Andreas Joseph Krogh <andreak@officenet.no>
To: pgsql-sql@postgresql.org
Subject: Problem with query on history table
Date: Mon, 27 Feb 2006 08:19:30 +0100
Message-ID: <200602270819.30702.andreak@officenet.no> (raw)
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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreak@officenet.no
Subject: Re: Problem with query on history table
In-Reply-To: <200602270819.30702.andreak@officenet.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox