Skip to Content
0

How build up a SALES query

May 10 at 01:59 PM

44

avatar image

How build up a query which shows:

Average # of order lines per sales order per month

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Nagarajan K May 10 at 02:03 PM
0

Hi,

Please ask only one question per discussion to track and get fast solution.

Regards,

Nagarajan

Show 1 Share
10 |10000 characters needed characters left characters exceeded

ok, now I've edited:

How build up a query which shows:

Average # of order lines per sales order per month

can you help on that?

0
Nagarajan K May 10 at 02:38 PM
0

Hi,

Try this query for sales order per month and avg for selected period.

Select isnull([1],0) as Jan, isnull([2],0) as Feb, isnull([3],0) as Mar, isnull([4],0) as Apr, isnull([5],0) as May, isnull([6],0) as June, isnull([7],0) as July, isnull([8],0) as Aug, isnull([9],0) as Sept, isnull([10],0) as Oct, isnull([11],0) as Nov, isnull([12],0) as Dec,

"AVG" = sum( isnull([1],0) + isnull([2],0)+ isnull([3],0) + isnull([4],0) + isnull([5],0) + isnull([6],0) + isnull([7],0) + isnull([8],0) + isnull([9],0) + isnull([10],0) + isnull([11],0) + isnull([12],0))/12

from

( SELECT Count(T0.[DocNum]) as T,month(T0.[DocDate]) as month FROM ORDR T0 WHERE T0.[CANCELED] = 'N' and T0.[DocDate] between [%0] and [%1] group by T0.[DocDate]) P

Pivot (Count(T) For month in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))P

group by [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

Show 1 Share
10 |10000 characters needed characters left characters exceeded

thanks!! :) just to better understand as I have doubts on figures I see, this query shows me number of SO posted in a month together with the average? looks strange as I expect higher figures...

Else, I see the query does not consider RDR1 so the order lines.

Thanks for help.

Mc

0
Nagarajan K May 10 at 10:03 PM
0

The above query is not based on lines, based on documents. Change above query as per your requirement and you can use same logic for delivery and credit memo.

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Not working as I am not sure this display what I am looking for.

it display in January for example only 22 orders while I've posted more than 300 SO in the month.

I do not understand where this 22 comes from...

0

Hi there, I am still struggling in having sorted out this.

How can I display the number or SO posted in a month correctly?

0