Skip to Content

Production Schedule by Date with Price

Hello All

I need two queries and I am getting an error with it.

  • Query to bring up all production orders open and closed scheduled for a given date range.

I have done the below, but I am getting an error

Can someone please have a look and advise where I am going wrong?

SELECT T0.[DueDate],

T0.[DocNum],

T0.[U_CustName],

T0.[PlannedQty],

T2.[Dscription],

T1.[PlannedQty],

T2.[LineTotal]

FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry, RDR1 T2

WHERE T0.[DueDate] = >[%0] T0.[DueDate] <[%1]

ORDER BY T0.[DocNum]

  • I need another similar query but which shouldn’t display any production orders that don’t have the Item Code “labour “in the BOM

SELECT T0.[DueDate],

T0.[DocNum],

T0.[U_CustName],

T0.[PlannedQty],

T2.[Dscription],

T1.[PlannedQty],

T2.[LineTotal]

FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry, RDR1 T2

WHERE T0.[DueDate] = >[%0] T0.[DueDate] <[%1] T1.[ItemCode] IS NOT NULL[%2]

ORDER BY T0.[DocNum]

Thanks and regards

Rahul

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 20, 2013 at 12:30 AM

    Hi Rahul,

    Try this:

    SELECT T0.[DueDate], T0.[DocNum], T0.[U_CustName], T0.[PlannedQty], T3.[Dscription], T1.[PlannedQty], T3.[LineTotal] FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry left join ORDR T2 on T2.[DocNum] = T0.[OriginNum] left JOIN RDR1 T3 ON T2.DocEntry = T3.DocEntry and T3.ItemCode=T0.ItemCode WHERE T0.[DueDate] >= [%0] and T0.[DueDate] <= [%1] ORDER BY T0.[DocNum]


    Make sure you enter a longer duration at first.


    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 19, 2013 at 12:25 AM

    Hi,

    1. There is no proper link between RDR1 and OWOR tables. If you run without correct join, system performance will be affected.

    2. Try this query:

    SELECT T0.[DueDate], T0.[DocNum], T0.[U_CustName], T0.[PlannedQty], T3.[Dscription], T1.[PlannedQty], T3.[LineTotal] FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry left join ORDR T2 on T2.[DocNum] = T0.[OriginNum] and T2.[CardCode] = T0.[CardCode] INNER JOIN RDR1 T3 ON T2.DocEntry = T3.DocEntry WHERE T0.[DueDate] >= [%0] and T0.[DueDate] < [%1] ORDER BY T0.[DocNum]

    3. As per forum rule, one question per thread allowed. For second query,please post as new discussion.

    Thanks & Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 10, 2014 at 04:25 AM

    Hi Rahul

    Find the Query Below

    SELECT T0.[DueDate],T0.[DocNum],T0.[U_CustName],T0.[PlannedQty],T3.[Dscription],

    T1.[PlannedQty],T3.[LineTotal]

    FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry,

    ORDR T2 INNER JOIN RDR1 T3 ON T2.DocEntry = T3.DocEntry

    WHERE T0.[DueDate] >='[%0]' AND T0.[DueDate]<='[%1]'

    AND T0.OriginNum = T2.DocNum

    ORDER BY T0.[DocNum]



    Regards

    Jenny

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.