Skip to Content

Help With Query To Check Monthly Build

Hello Experts

We have items that sometimes need to be worked on in the production area and we manage it by using Labour in the production order

Then we have items which come in complete and don't need to be worked on and since it won't be going into production we don't use Labour in the production order

I have written a query which result should tell me the date the production orders are scheduled time allocated and so on

The query works but I am not getting all the info I need

The results of the query only display's the production orders with Labour in it, it doesn't display the production order without labour

Can someone please check my query and tell me what am I doing wrong

I need the results to display all production orders in the date range, with and without labour

SELECT

T0.[DueDate] as 'Build Date',

T0.[DocNum] as 'Prod Ord',

T0.[U_CustName] as 'Sales Partner',

T0.[PlannedQty] as 'Qty',

T0.[ItemCode] as 'Product No',

T2.[ItemName] as 'Product Description',

T1.[PlannedQty] as 'Time',

T0.[Comments] as 'Remarks',

T3.[ItmsGrpNam] as 'Item Group',

T0.[Status] as 'Prod Status',

T4.[ReqDate] as 'Required Date',

T5.[ShipDate] as 'Despatch Date',

T5.[LineTotal] as 'Row Value'


FROM

OWOR T0 INNER JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode

INNER JOIN OITB T3 ON T2.[ItmsGrpCod] = T3.[ItmsGrpCod]

LEFT JOIN ORDR T4 ON T4.[DocNum] = T0.[OriginNum]

LEFT JOIN RDR1 T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode and T5.[U_Prod_Order_No] = T0.[DocNum]


WHERE

T1.[ItemCode] <> 'SUNDRY LABOUR' and t1.docentry NOT IN ( select t1.docentry from wor1 t1 where t1.itemcode = 'SUNDRY LABOUR') and

T1.[ItemCode] <> 'OUTSOURCED' and t1.docentry NOT IN ( select t1.docentry from wor1 t1 where t1.itemcode = 'OUTSOURCED') and

T0.[DueDate] between [%0] and [%1] and

T1.[ItemCode] = 'LABOUR' and

T0.[Status] not in ( 'C')


GROUP BY

T0.[DocNum], T0.[DueDate], T0.[U_CustName], T0.[PlannedQty], T0.[ItemCode], T2.[ItemName], T1.[PlannedQty], T0.[Comments], T3.[ItmsGrpNam], T0.[Status], T4.[ReqDate], T5.[ShipDate], T5.[LineTotal]


ORDER BY

T0.[DueDate], T0.[DocNum]

Have another question, I have a column that shows me the status. This status only shows the letter L for closed, C for cancelled and so on

Is it possible to show the whole word in the result Closed instead of just L?


Thanks and Regards

Rahul

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Jul 06, 2016 at 12:50 AM

    Hi Rahul,

    Please remove condition T1.[ItemCode] = 'LABOUR' and then run again.


    Thanks

    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.