Skip to Content
avatar image
Former Member

Vendor on time delivery query

Hello Experts,

     I'm looking for a query that will report the average days late for all of our vendors within a particular date range.  Would any of you have a query that will report these averages by vendor or know of a report already in SBO 9.0 that will perform this evaluation? 

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Jan 13, 2015 at 12:11 AM

    Hi,

    There is no standard report for late delivery. We have to create query to get average delays.

    Thanks & Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 11, 2015 at 07:53 PM

    Ok Gentlemen,

         I have received the Mastering SQL Queries for SBO book and have read through it.  It is an excellent resource and I am most positive that I will be referring to it quite often but I still need a little guidance.

         The query below is what I have ended up with thus far.  I plan to export the query into excel and use the subtotal function to calculate the average days late for each vendor.  The dilemma I have now is that the current query returns the 'Delay' as both early deliveries (calculation returns negative number), on time (result of ''), as well as late ones (calculated numbers are positive). 

         All I need are late deliveries.  The 'Delay' that is > 0.  What would be the best way to "exclude" the 'Delay' that is <= 0 and only return the positive calculations?  Or if you look at it the other way... only return the values of 'Delay' that or > 0. 

    SELECT T0.[DocNum] 'PO #', T0.[DocDate] 'PO Posting Date', T1.[ShipDate] 'PO Line Item Due Date', T0.[DocDueDate] 'PO Due Date', T0.[CardName], T0.[NumAtCard], IsNull(T3.[DocDate],'') 'GRPO Date', DateDiff (dd, ISNULL(T1.ShipDate,T0.DocDueDate),T3.DocDate) 'Delay'

    FROM OPOR T0

    INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry

    LEFT JOIN PDN1 T2 ON T0.DocEntry=T2.BaseEntry AND T1.LineNum=T2.BaseLine

    LEFT JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry

    WHERE T0.DocDate>= [%0]  and T0.DocDate<=[%1]

    Add comment
    10|10000 characters needed characters exceeded

    • If you use ISNULL, then above query will fetch all PO (item and service type). Without ISNULL, then query will fetch only Item type PO. That's why there is diffference in query result.

  • Jan 21, 2015 at 01:31 PM
    Add comment
    10|10000 characters needed characters exceeded

  • Jan 26, 2015 at 01:15 AM

    Any update? Did you find the book?

    Add comment
    10|10000 characters needed characters exceeded