Skip to Content
0

Query Generator- Freight Analysis

Mar 06, 2017 at 03:46 AM

147

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Johan Hakkesteegt Mar 06, 2017 at 07:27 AM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Johan,

Thanks for your assistance- but your suggestion actually yields less results than before.

To clarify- all of the fields in the above query should always include data- so I am wondering if it is a problem with the type of joining I have used to link the tables.

Any assistance would be greatly appreciated.

Thanks,

Lauren

0

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

0
Luke Dunn Apr 14, 2017 at 01:24 PM
0

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

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

0