Re: need some help on figuring out how to write a query

Em Thursday 21 February 2008 18:37:47 Justin escreveu:

> Now i could write a function to do this or do it in C++ program that
> creates query with all kinds of unions. I'm wondering if there is a way
> to create this in a single select statement??
> I can't think of a way to do it???

Why you need it in one query? Think of maintenability not on code size.

Solve the problem in parts, calculating it for one week -- or ten, you can use
the interval type -- and then moving on...

The function would look like:

WHILE start_date + '10 weeks'::interval < today:
SELECT INTO weeks_avg avg(value) FROM table WHERE date BETWEEN start_date AND
start_date+'10 weeks'::interval;
start_date:=start_date + '1 week'::interval;
END WHILE;

Start from that and you'll have it done. (Of course, above is pseudo code and
untested...)

--
Jorge Godoy <jgodoy(at)gmail(dot)com>