Skip to Content

Query Generator- Freight Analysis

Hi experts,

I am creating a new query to manage our freight costs- looking at budgeted versus actual, based on delivery dockets with UDFs for Freight Carrier details. But when I run the below query- it doesnt bring up all delivery dockets generated during the dates selected?

SELECT T0.[DocDate] AS 'Delivery Date', T0.[DocNum] AS 'Delivery Docket', T0.[CardName] AS 'Customer Name', T4.[CityS], T4.[StateS], T3.[SlpName] AS 'Sales Rep', T1.[Dscription] AS 'Freight Description', T0.[U_FreightCarrier], T1.[Price] AS 'Freight Cost to Customer', T0.[U_FreightCost] AS 'Freight Cost to VEF', T1.[Price] - T0.[U_FreightCost] AS 'Freight Win or Loss', (T1.[Price] /(T0.[DocTotal] - T0.[VatSum] - T1.[Price])*100) AS 'Freight %', T0.[Comments]

FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode INNER JOIN DLN12 T4 ON T0.DocEntry = T4.DocEntry

WHERE T0.[DocDate] >= [%1] AND T0.[DocDate] <= [%2] AND T2.[U_Range] IN ('FREIGHT OUT')

ORDER BY T0.[DocDate], T0.[U_FreightCarrier]

Any help would be greatly appreciated.

Many thanks,

Lauren

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Mar 06, 2017 at 07:27 AM

    Hi Lauren,

    Please give this a try:

    SELECT T0.[DocDate] AS 'Delivery Date'
         , T0.[DocNum] AS 'Delivery Docket'
     , T0.[CardName] AS 'Customer Name'
     , T4.[CityS]
     , T4.[StateS]
     , T3.[SlpName] AS 'Sales Rep'
     , T1.[Dscription] AS 'Freight Description'
     , T0.[U_FreightCarrier]
     , T1.[Price] AS 'Freight Cost to Customer'
     , T0.[U_FreightCost] AS 'Freight Cost to VEF'
     , T1.[Price] - T0.[U_FreightCost] AS 'Freight Win or Loss'
     , (T1.[Price] /(T0.[DocTotal] - T0.[VatSum] - T1.[Price])*100) AS 'Freight %'
     , T0.[Comments]
    FROM ODLN T0 
         INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry 
     INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 
     LEFT OUTER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode 
     LEFT OUTER JOIN DLN12 T4 ON T0.DocEntry = T4.DocEntry
    WHERE T0.[DocDate] >= [%1] 
      AND T0.[DocDate] <= [%2] 
      AND AND ISNULL(T2.[U_Range], '') LIKE ('%FREIGHT%OUT%')
    ORDER BY T0.[DocDate]
           , T0.[U_FreightCarrier]

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Lauren,

      "...should always include data.." are famous last words in query land :-)

      Along the lines of what Luke already suggested, you should take a look at a specific delivery in the query result, that is not yielding all the data that you would expect, or a delivery that is missing completely from the result. Compare the parameters that you input in the query with this delivery, and see how/where they do not match up. one of the most likely culprits is the U_Range field. In the query below I fixed the joining to be as loose as possible, and I commented out three different versions of the U_Range parameter, that may each yield more results. Please try one of them at a time.

      SELECT T0.[DocDate] AS 'Delivery Date'
           , T0.[DocNum] AS 'Delivery Docket'
           , T0.[CardName] AS 'Customer Name'
           , T4.[CityS]
           , T4.[StateS]
           , T3.[SlpName] AS 'Sales Rep'
           , T1.[Dscription] AS 'Freight Description'
           , T0.[U_FreightCarrier]
           , T1.[Price] AS 'Freight Cost to Customer'
           , T0.[U_FreightCost] AS 'Freight Cost to VEF'
           , T1.[Price] - T0.[U_FreightCost] AS 'Freight Win or Loss'
           , (T1.[Price] / (T0.[DocTotal] - T0.[VatSum] - T1.[Price]) * 100) AS 'Freight %'
           , T0.[Comments]
      FROM ODLN T0 
           LEFT OUTER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry 
           LEFT OUTER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode 
           LEFT OUTER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode 
           LEFT OUTER JOIN DLN12 T4 ON T0.DocEntry = T4.DocEntry
      WHERE T0.[DocDate] BETWEEN [%1] AND [%2]
       /* AND UPPER(ISNULL(T2.[U_Range],'')) LIKE ('%OUT%') */
       /* AND UPPER(ISNULL(T2.[U_Range],'')) LIKE ('%FR%T%T%') */
       /* AND UPPER(ISNULL(T2.[U_Range],'')) LIKE ('%FREIGHT%OUT%') */
      ORDER BY T0.[DocDate]
             , T0.[U_FreightCarrier]

      Regards,

      Johan

  • avatar image
    Former Member
    Apr 14, 2017 at 01:24 PM

    Hi Lauren,

    From looking at this query you seem to have a where clause where the item master needs to have UDF "U_range populated with "Freight out" this would potentially filter out documents.

    Can you please find one of the documents that you would expect to see in your query above, check the item master on the lines and see if these are populated with "freight out"

    I hope this helps

    Kind regards

    -Luke

    Add comment
    10|10000 characters needed characters exceeded

    • Thanks Luke & Johan for your help-

      After pouring through all of our delivery dockets created this year- I have realized that the error was with the user not always including a freight item ([U_Range] IN 'FREIGHT OUT') in their delivery dockets- rather than an issue with the query itself.

      Thank you both,

      Lauren