Skip to Content
0

Sales Order Delivery Date Vs Delivery Note Delivery Date

Apr 10, 2017 at 10:09 AM

224

avatar image
Former Member

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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Johan Hakkesteegt Apr 11, 2017 at 06:48 AM
0

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

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

Hi Johan

It works perfectly! Many thanks for your help - much a appreciated :)

0
Johan Hakkesteegt Apr 10, 2017 at 12:56 PM
0

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

Show 1 Share
10 |10000 characters needed characters left 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)
0