Skip to Content

Query to check Urgent Production Orders

Hi Experts

I have written a query to check for urgent production order requests

It works fine when the production order is linked to a sales order, but as soon as a production order is raised on it's own and isnt linked to sales order

It doesnt appear in the query results?

Can someone please help me find where i am going wrong?

SELECT     


TOP (100) PERCENT 


T2.DueDate AS [Build Date], 


T1.ShipDate AS [Must Despatch], 


T0.DocNum AS [Sales Order], 


T0.CardName AS [Sales Partner],                       


T1.U_Prod_Order_No AS [Prodn.Order], 


T1.Quantity AS Qty, 


T1.ItemCode AS [Product Code], 


T1.Dscription AS [Product Description], 


T2.Comments


FROM         


dbo.ORDR AS T0 INNER JOIN dbo.RDR1 AS T1 ON T0.DocEntry = T1.DocEntry



INNER JOIN dbo.OWOR AS T2 ON ISNULL(T1.U_Prod_Order_No, - 1)
= T2.DocNum


WHERE     


(T2.Status IN ('P', 'R')) AND 


(T1.ShipDate <> CONVERT(DATETIME, '2033-03-03
00:00:00', 102)) AND 


(T1.ShipDate <> CONVERT(DATETIME, '2044-04-04
00:00:00', 102)) AND 


(T1.ShipDate <> CONVERT(DATETIME, '2033-03-04
00:00:00', 102)) AND 


(T1.ShipDate >= GETDATE() - 1) AND 


(T1.ShipDate <= GETDATE() + 20)


GROUP BY 


T0.DocNum, T1.ShipDate, T0.CardName, T1.U_Prod_Order_No,
T1.Quantity, T1.ItemCode, T1.Dscription, T2.Comments, T2.DueDate


HAVING      


(T2.Comments LIKE N'%%URGENT%%') OR


(T2.Comments LIKE N'%%urgent%%') OR


(T2.Comments LIKE N'%%PRIORITY%%')


ORDER BY [Must Despatch], [Sales Partner], [Sales Order]

Thanks and Regards

Rahul

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Mar 22 at 07:35 AM

    Hi Rahul,

    The problem is that you are starting your FROM clause with ORDR (sales orders).

    You need to start with OWOR and then LEFT OUTER JOIN RDR1 and ORDR.

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Rahul,

      The NULLs are also correct as they are taken from the sales order rows, which don't exist.

      You can circumvent these with ISNULL, or use COALESCE and join in the WOR1 table to get corresponding data from the production order rows.

      For example:

      COALESCE(T1.Quantity, WOR1.[PlannedQty], 0) AS Qty

      Regards,

      Johan

  • Mar 29 at 11:58 PM

    Hi Rahul,

    Let me know still this issue not solved.

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 30 at 12:25 PM

    Hi Guys

    I have tried the using the LEFT OUTER JOINS, but the production order that is not linked to a sales order does not appear in the query results

    Below is the query I have modified from the original I posted, this one uses Augusto's join which I had to change a bit because I was getting errors

    SELECT     
    TOP (100) PERCENT 
    T2.DueDate AS [Build Date], 
    T1.ShipDate AS [Must Despatch], 
    T0.DocNum AS [Sales Order], 
    T0.CardName AS [Sales Partnet], 
    T1.U_Prod_Order_No AS [Prodn.Order], 
    T1.Quantity AS Qty, 
    T1.ItemCode AS [Item Code], 
    T1.Dscription AS [Item Description], 
    T2.Comments AS Remarks
    FROM         
    dbo.OWOR AS T2 LEFT OUTER JOIN dbo.RDR1 AS T1 ON ISNULL(T1.U_Prod_Order_No, - 1) = T2.DocNum 
    LEFT OUTER JOIN dbo.ORDR AS T0 ON T0.DocEntry = T1.DocEntry
    WHERE     
    (T2.Status IN ('P', 'R')) AND (
    (T1.ShipDate <> CONVERT(DATETIME, '2033-03-03 00:00:00', 102)) AND 
    (T1.ShipDate <> CONVERT(DATETIME, '2044-04-04 00:00:00', 102))AND 
    (T1.ShipDate <> CONVERT(DATETIME, '2033-03-04 00:00:00', 102)) AND 
    (T1.ShipDate >= GETDATE() - 1) AND 
    (T1.ShipDate <= GETDATE() + 14)
    GROUP BY T2.DueDate, T1.ShipDate, T0.DocNum, T0.CardName, T1.U_Prod_Order_No, T1.Quantity, T1.ItemCode, T1.Dscription, T2.Comments
    HAVING      
    (T2.Comments LIKE N'%%URGENT%%') OR
    (T2.Comments LIKE N'%%urgent%%') OR
    (T2.Comments LIKE N'%%PRIORITY%%')
    ORDER BY [Must Despatch], [Sales Order], [Sales Partnet]
    
    

    Regards

    Rahul

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 29 at 05:57 AM

    Hi Tahul,

    Where you have the FROM till the WHERE replace by

    FROM

    dbo.OWOR T2 left outer join dbo.RDR1 AS T1 ISNULL(T1.U_Prod_Order_No,- 1)= T2.DocNum left outer join OWOR T0 ON T0.DocEntry = T1.DocEntry

    WHERE

    Best regards,

    Augusto

    Add comment
    10|10000 characters needed characters exceeded