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

I'm not after small code i'm after maintainable code where the date
range for this report would be always changing. So if i can get it to
one select statement i would on have to pass in some variables and it
would create the moving average. Plus what if the sales people decide
they want to change moving average from 10 weeks to 5 weeks or change it
to 15 weeks. People drive me nuts with i want it to do this or that ,
Of course they have no idea how complicated it sometimes to get what
they want.

Thanks you for your ideas Dean and Jorge gives me some ideas to play with.

Jorge Godoy wrote:
> 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...)
>
>