Skip to Content
0
Jul 06, 2016 at 12:28 AM

Help With Query To Check Monthly Build

51 Views

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