Обсуждение: 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:
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>