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

need to write a query for sales order table where we get the qty order *
price for all line items by week then average then create a moving
Average past 10 weeks of entered sales orders line items.

So what will happen is some one will enter a date range going back 6
month from the present date then query will need to get all the sales
order line items that falls in that date range by week, then average the
first 10 weeks,  drop of the first week moving to the 2nd week to the
11th week to create another average, then drops 2nd week off then moves
3rd week to the 12th week in the query date range create another average
and so  and so till the end of the date range. This will create moving
average for sales order entered.

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???

On 2008-02-21 13:37, Justin wrote:
> ...  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???
Break down your problem using VIEWs.  Create a VIEW that gets just ONE
of the averages, based on a starting date.  Then create a SELECT that
gets data from the VIEW as though it was an actual table.

Once you get it working, you can replace the reference to the VIEWs in
the SELECT statement, with the definition of the VIEWs, but I would not
do that unless the result is relatively simple and easy to understand.
Maintainability should be your goal.

-- Dean

--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.

On Thu, Feb 21, 2008 at 8:09 PM, Dean Gibson (DB Administrator)

>  Mail to my list address MUST be sent via the mailing list.
>  All other mail to my list address will bounce.

Totally uncool.  There's settings in the mail server software that
will change your outgoing messages to not be from you but from the
list, so that reply and reply all both go to pgsql-xxx@postgres.org
and not you.

Not that I'll see the bounce messages.  Most go in the spam bin.  But
still it creates load on everyone else when there's a setting to fix
it.  Since I like to receive both direct and from the list, and my
client knows that they're the same message so it doesn't show it
twice, I use the default settings and don't know how to change the
mailing list manager stuff.  But it's been posted here dozens of
times.

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@gmail.com>

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

Em Friday 22 February 2008 13:10:20 Justin escreveu:
> 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.

Then just add more parameters to your function.  Instead of just start date
also include an optional period, in weeks, days, hours, whatever you think is
the better granularity for this.

So, in your interface, add the period and make "10" the default value.



--
Jorge Godoy      <jgodoy@gmail.com>