Skip to Content

SAP B1 Query # of deliveries per sales order per month

Hi, I am looking for a query which shows me how many deliveries has been done per sales order per month.

Can some expert give me a tip?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    May 18 at 11:57 AM

    Hi Mauro,

    Here is a simple query that shows amount of deliveries per sales order over the last month:

    SELECT h.DocNum
          ,h.CardCode
          ,h.CardName
          ,d.Dels AS [Nr of Deliveries]
    FROM ORDR h
         INNER JOIN RDR1 r ON h.DocEntry = r.DocEntry
         LEFT OUTER JOIN (select dr.BaseEntry
                               , count(*) as Dels
                          from DLN1 dr
    			   inner join ODLN dh on dr.DocEntry = dh.DocEntry
    		      where dr.BaseType = 17
                          group by dr.BaseEntry) d ON h.DocEntry = d.BaseEntry
    WHERE h.DocDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE() /* this means one month back from now */
    GROUP BY h.DocNum
            ,h.CardCode
            ,h.CardName
            ,d.Dels
    

    Perhaps you can adapt it to your needs.

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Mauro,

      That is an entirely different query than the one above. Please post a new question with a little more detailed request, and I am sure, if not me, someone will help you out.

      Regards,

      Johan

  • May 17 at 02:57 PM

    Hi

    try this SQL statement:

    SELECT [1] as [Jan], [2] as [Feb], [3] as [Mar], [4] as [Apr], [5] as [May], [6] as [Jun], [7] as [Jul], [8] as [Aug], [9] as [Sep], [10] as [Oct], [11] as [Nov], [12] as [Dec]FROM (SELECT Left(T0.CardCode,2) as [State], T0.DocTotal as [DocTotal], MONTH(T0.docdate) as [month] FROM dbo.oinv T0 WHERE Year(T0.docdate)=2018 UNIONSELECT Left(T0.CardCode,2) as [State], -T0.DocTotal as [DocTotal], MONTH(T0.docdate) as [month] FROM dbo.orin T0 WHERE Year(T0.docdate)=2018 )  S  PIVOT  (SUM(DocTotal) FOR [month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P 

    Kind regards

    Agustín

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Agustin, I see strange figures and I do not understand.

      I am looking to know how many deliveries per sales order have been generated in a month.

      I tried your statement via query generator in SAP B1 but I realize I see amounts, not number of deliveries generated by every sinlge order.

      Thanks for help.

      Kind regards,

      Mauro