Re: Calculation of per Capita on-the-fly - problems with SQL syntax

On Oct 15, 2007, at 10:50 , brian wrote:

> Adjust your data so that table agri_area has a row for each year?

I can imagine that some of his raw data is a multi-year aggregate, so
it may be difficult for him to do this.

> Otherwise, i think you'd need to parse your year column by
> expanding any entries with a dash into a range.

I suggest using two *date* (or possibly integer) columns for each row
and consider each row an year interval (in the mathematical sense,
not to be confused with SQL intervals, which are actually durations).
Depending on the interval representation you choose (closed-open or
closed-closed), a single-year interval (say, 1970), would be
represented as either ('1970-01-01', '1970-01-01') or ('1970-01-01',
'1971-01-01').

The reason I suggest using dates rather than integers is that it
gives you more flexibility for the future, and you have access to all
of the date functions available in Postgres that you will probably
find useful.

> But i'm not sure that you could then compare a range against a list
> (WHERE ... IN ... )

The only reference for managing temporal data in ANSI SQL I'm aware
of is Snodgrass' "Developing Time-Oriented Database Applications in
SQL"[1]. Date, Darwen, and Lorentzos' "Temporal Data and the
Relational Model"[2] is a more general text on the same topic, but
isn't directly applicable to ANSI SQL. I suggest looking at least at
the first.

Hope this helps.

Michael Glaesemann
grzm seespotcode net

[1](http://www.cs.arizona.edu/~rts/tdbbook.pdf)
[2](http://www.amazon.com/gp/product/1558608559/)