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

[Justin <justin@xxxxxxxxxxxxxxx>]

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...)