Skip to Content

How build up a SALES query

How build up a query which shows:

Average # of order lines per sales order per month

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    May 10 at 02:03 PM

    Hi,

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

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • May 10 at 02:38 PM

    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]

    Add comment
    10|10000 characters needed 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

  • May 10 at 10:03 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded