Skip to Content
avatar image
Former Member

Sales Order Delivery Date Vs Delivery Note Delivery Date

Hi Gurus

I am needing a bit of help in adapting a query. Instead of displaying the Vendor details it must display the Customer details.

The goal of this report is to identify the delivery date on the Sales Orders and Compare it to the delivery date on the Delivery documents so that they can measure how much time is being lost due to delays. Ideally it would be identical to the screenshot (albeit that it displays Customer details):

SELECT T0.[DocNum], T0.[DocDate] , T0.[DocDueDate] , T0.[CardName] , T0.[NumAtCard] , T3.[DocDate] , cast(DateDiff (dd, T0.DocDueDate,T3.DocDate) as Numeric) as Delay

FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry LEFT JOIN PDN1 T2 ON T0.DocEntry=T2.BaseEntry and T2.[BaseLine] = T1.[LineNum] INNER JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry

WHERE T0.DocDate>= [%0] and T0.DocDate<=[%1] group by T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardName], T0.[NumAtCard], T3.[DocDate]

macneil-query.png (60.0 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 11, 2017 at 06:48 AM

    Hi Bruce,

    Sorry about that, I explained it, but I didn't listen ;-) OPOR should be ORDR and POR1 should be RDR1, so:

    SELECT T0.[DocNum]
         , T0.[DocDate]
         , T0.[DocDueDate]
         , T0.[CardName]
         , T0.[NumAtCard]
         , T3.[DocDate]
         ,cast(DateDiff(dd, T0.DocDueDate, T3.DocDate)as Numeric) as Delay
    FROM ORDR T0
         INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
         INNER JOIN DLN1 T2 ON T0.DocEntry=T2.BaseEntry
                     AND T1.[LineNum] = T2.[BaseLine]
         INNERJOIN ODLN T3 ON T2.DocEntry = T3.DocEntry
    WHERE T0.DocDate BETWEEN [%0] AND [%1]
    GROUP BY T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardName], T0.[NumAtCard], T3.[DocDate]

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 10, 2017 at 12:56 PM

    Hi Bruce,

    This is fortunately a rather easy one, because the table structures of all documents are more or less the same. That means that you only need to figure out what the necessary tables are for orders and deliveries (in the current query its the tables for PO and GRPO):

    SELECT T0.[DocNum]
         , T0.[DocDate]
         , T0.[DocDueDate]
         , T0.[CardName]
         , T0.[NumAtCard]
         , T3.[DocDate]
         , cast(DateDiff (dd, T0.DocDueDate,T3.DocDate) as Numeric) as Delay
    FROM OPOR T0
         INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry
         LEFT JOIN DLN1 T2 ON T0.DocEntry=T2.BaseEntry
                       AND T2.[BaseLine] = T1.[LineNum]
         INNER JOIN ODLN T3 ON T2.DocEntry = T3.DocEntry
    WHERE T0.DocDate>= [%0] 
      AND T0.DocDate<=[%1]
    GROUP BY T0.[DocNum]
           , T0.[DocDate]
           , T0.[DocDueDate]
           , T0.[CardName]
           , T0.[NumAtCard]
           , T3.[DocDate]

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Johan,

      Many thanks for the speedy response!

      It works prefectly on one of the DBs I tested however on the DB that I need it to work on, no matter the dates I input (typically for 2017), it only displays data for 2010. Any ideas what might be causing this?

      Many thanks in advance,

      - Bruce

      2.png (61.4 kB)