Production Schedule by Date with Price

[RahF]

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

[Former Member · Accepted solution]

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

[RahF · Accepted solution]

Hi Gordon

I tried your query

But it is showing muliple entries and quantites in the results

The actual quantity is only 12 and the Total value 13831.20$

See attached

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query:

SELECT T0.[DueDate], T0.[DocNum], T0.[PlannedQty], T3.[Dscription], sum(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] group by T0.[DueDate], T0.[DocNum], T0.[PlannedQty], T3.[Dscription],T3.[LineTotal]   ORDER BY T0.[DocNum]

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Try:

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

FROM OWOR T0

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]

[RahF · Accepted solution]

Hi Nagarajan

It displays the correct results if the production orders have been completed

But for production orders that haven't been completed

It is showing multiple results and incorrect row totals

Is there a way if displays the correct row total even if the production order isnt completed?

Thanks and regards

Rahul

[RahF · Accepted solution]

Hi Gordon

It displays the correct results if the production orders have been completed

But for production orders that haven't been completed

It is showing multiple results and incorrect row totals

Is there a way if displays the correct row total even if the production order isnt completed?

Thanks and regards

Rahul

[Former Member · Accepted solution]

Hi Rahul Fernandez,

Try to put production status in selection criteria in query...