on 07-06-2016 1:28 AM
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
Hi Rahul,
Please remove condition T1.[ItemCode] = 'LABOUR' and then run again.
Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Nagarajan
I can't remove the condition T1.[ItemCode] - 'LABOUR'
I am identifying the condition T1.[PlannedQty] as 'Time' using it
In a production order in the WOR1 table, we have all the components and one of those components is Labour.
If I remove it, how can I get the query result to display the quantity as time?
Please see attached
Thanks and Regards
Rahul
Hi Rahul
Try bellow query
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',
(select S1.[PlannedQty] from WOR1 S1 where S1.DocEntry = T0.DocEntry and S1.ItemCode = 'LABOUR') as 'Time',
T0.[Comments] as 'Remarks',
T3.[ItmsGrpNam] as 'Item Group',
case when T0.[Status] = 'P' then 'Planned'
when T0.[Status] = 'R' then 'Released'
when T0.[Status] = 'L' then 'Closed'
when T0.[Status] = 'C' then 'Canceled' end 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
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]
Based on your query condition, it will display those production order which is having labour item will be displayed.
If you want to get all productions with or without labor item, you should remove Itemcode = 'labour' from your query.
For planned time, Isnull(T1.[PlannedQty]),0) as time --> Meaning those production order without labor displayed as 0 time
Hi Nagarajan
No that isn't the result I am after
I think Gaurav is on the right track, but it's not working
There is something wrong in the below he wrote
(select S1.[PlannedQty] from WOR1 S1 where S1.DocEntry = T0.DocEntry and S1.ItemCode = 'LABOUR') as 'Time',
I think if the above works that would be the result I am after
I have attached screen caps of my results
One with the Labour condition and one without
The one without the labour condition, shows multiple results if there is an labour Item code in the production order
Thanks and Regards
Rahul
here is the query I didn't tested it first time. I had to remove some UDFs though hope you can convert rest to your needs.
SELECT
T0.[DueDate] as 'Build Date',T0.[DocNum] as 'Prod Ord',T0.[PlannedQty] as 'Qty',T0.[ItemCode] as 'Product No',T2.[ItemName] as 'Product Description',
(select SUM(S1.[PlannedQty]) from WOR1 S1 where S1.DocEntry = T1.DocEntry and S1.ItemCode = 'LABOUR' GROUP BY S1.DocEntry) as 'Time',T0.[Comments] as 'Remarks',T3.[ItmsGrpNam] as 'Item Group',
case when T0.[Status] = 'P' then 'Planned'
when T0.[Status] = 'R' then 'Released'
when T0.[Status] = 'L' then 'Closed'
when T0.[Status] = 'C' then 'Canceled' end 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 '20140101' and '20150401' and
T0.[Status] not in ( 'C')
GROUP BY
T0.[DocNum], T0.[DueDate], T0.[PlannedQty], T0.[ItemCode], T2.[ItemName], T1.[PlannedQty], T0.[Comments], T3.[ItmsGrpNam], T0.[Status], T4.[ReqDate], T5.[ShipDate],
T5.[LineTotal],T1.DocEntry
ORDER BY
T0.[DueDate], T0.[DocNum]
Hi Gaurav
I tried it but i am getting an (SWEI) error?
The below is what I have done...
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',
(select SUM(S1.[PlannedQty]) from WOR1 S1 where S1.DocEntry = T1.DocEntry and S1.ItemCode = 'LABOUR' GROUP BY S1.DocEntry) as 'Time',
T0.[Comments] as 'Remarks',
T3.[ItmsGrpNam] as 'Item Group',
case when T0.[Status] = 'P' then 'Planned'
when T0.[Status] = 'R' then 'Released'
when T0.[Status] = 'L' then 'Closed'
when T0.[Status] = 'C' then 'Cancelled' end 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
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],T1.DocEntry
ORDER BY
T0.[DueDate], T0.[DocNum]
Thanks and Regards
Rahul
HI Rahul
Here is the complete Query Please check it should work with out any error
Declare @FDate datetime
Declare @TDate Datetime
Select @FDate = Min(F1.DueDate) from OWOR F1 where F1.DueDate >= [%0]
Select @TDate = Max(F2.DueDate) from OWOR F2 where F2.DueDate <= [%1]
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',
(select SUM(S1.[PlannedQty]) from WOR1 S1 where S1.DocEntry = T1.DocEntry and S1.ItemCode = 'manpower' GROUP BY S1.DocEntry) as 'Time',T0.[Comments] as 'Remarks',
T3.[ItmsGrpNam] as 'Item Group',
case when T0.[Status] = 'P' then 'Planned'
when T0.[Status] = 'R' then 'Released'
when T0.[Status] = 'L' then 'Closed'
when T0.[Status] = 'C' then 'Cancelled' end as 'Prod Status',
T4.[ReqDate] as 'Required Date',T5.[ShipDate] as 'Despatch Date',T5.[LineTotal] as 'Row Value'
FROM OWOR T0
INNER JOIN (select Docentry from WOR1 where ItemCode not in ('SUNDRY LABOUR','OUTSOURCED') group by DocEntry) 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
T0.[DueDate] between @Fdate and @Tdate and
T0.[Status] not in ( 'C')
GROUP BY
T0.[DocNum], T0.[DueDate],T0.[U_CustName], T0.[PlannedQty], T0.[ItemCode], T2.[ItemName], T0.[Comments], T3.[ItmsGrpNam], T0.[Status], T4.[ReqDate], T5.[ShipDate],
T5.[LineTotal],T1.DocEntry
ORDER BY
T0.[DueDate], T0.[DocNum]
The error comes due to error in conversion most probably in [U_Prod_Order_No]. the field needs to contain numbers only for linking To DocNum
run the query
select * from RDR1 where [U_Prod_Order_No] = 'Manually'
If Query returns any result you can add condition to remove the docentry from query or update SalesOrder Rows
Also Please update S1.ItemCode = 'manpower' To 'Labour' I forgot to update it.
Replace the line
LEFT JOIN RDR1 T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode and T5.[U_Prod_Order_No] = T0.[DocNum]
with
LEFT JOIN (select * from RDR1 S0 where S0.[U_Prod_Order_No] not in ('Manually')) T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode and T5.[U_Prod_Order_No] = T0.[DocNum]
The line will remove the lines from RDR1 containing Manually. Hope it solves the problem.
Also prefer to keep one datatype per field for consistency it will help to avoid lots of errors.
Use following Line also update all the values in Not in if others are present. This can be long and tedious if there are more such values. In such case It would be advisable not to use RDR1 on line leve at all
LEFT JOIN (select * from RDR1 S0 where S0.[U_Prod_Order_No] not in ('Manually','185-0744') T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode and T5.[U_Prod_Order_No] = T0.[DocNum]
alternatively if you are using 'Procurement confirmation Wizard' or sales order number field in production order is updated you can use following line for mapping RDR1 to OWOR
LEFT JOIN ORDR T4 ON T4.[DocEntry] = T0.[OriginAbs]
LEFT JOIN RDR1 T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode
Try this
Declare @FDate datetime
Declare @TDate Datetime
Select @FDate = Min(F1.DueDate) from OWOR F1 where F1.DueDate >= [%0]
Select @TDate = Max(F2.DueDate) from OWOR F2 where F2.DueDate <= [%1]
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',
(select SUM(S1.[PlannedQty]) from WOR1 S1 where S1.DocEntry = T1.DocEntry and S1.ItemCode = 'LABOUR' GROUP BY S1.DocEntry) as 'Time',T0.[Comments] as 'Remarks',
T3.[ItmsGrpNam] as 'Item Group',
case when T0.[Status] = 'P' then 'Planned'
when T0.[Status] = 'R' then 'Released'
when T0.[Status] = 'L' then 'Closed'
when T0.[Status] = 'C' then 'Cancelled' end as 'Prod Status',
T4.[ReqDate] as 'Required Date',T5.[ShipDate] as 'Despatch Date',T5.[LineTotal] as 'Row Value'
FROM OWOR T0
INNER JOIN (select Docentry from WOR1 where ItemCode not in ('SUNDRY LABOUR','OUTSOURCED') group by DocEntry) 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.[DocEntry] = T0.[OriginAbs]
LEFT JOIN RDR1 T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode
WHERE
T0.[DueDate] between @Fdate and @Tdate and
T0.[Status] not in ( 'C')
GROUP BY
T0.[DocNum], T0.[DueDate],T0.[U_CustName], T0.[PlannedQty], T0.[ItemCode], T2.[ItemName], T0.[Comments], T3.[ItmsGrpNam], T0.[Status], T4.[ReqDate], T5.[ShipDate],
T5.[LineTotal],T1.DocEntry
ORDER BY
T0.[DueDate], T0.[DocNum]
Hi Gaurav
I don't know why it won't work for me
I have attached screen shots
Below is the query. The only modification is the isnull that i added to it
Declare @FDate datetime
Declare @TDate Datetime
Select @FDate = Min(F1.DueDate) from OWOR F1 where F1.DueDate >= [%0]
Select @TDate = Max(F2.DueDate) from OWOR F2 where F2.DueDate <= [%1]
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',
Isnull((select SUM(S1.[PlannedQty]) from WOR1 S1 where S1.DocEntry = T1.DocEntry and S1.ItemCode = 'LABOUR' GROUP BY S1.DocEntry),0) as 'Time',T0.[Comments] as 'Remarks',
T3.[ItmsGrpNam] as 'Item Group',
case when T0.[Status] = 'P' then 'Planned'
when T0.[Status] = 'R' then 'Released'
when T0.[Status] = 'L' then 'Closed'
when T0.[Status] = 'C' then 'Cancelled' end as 'Prod Status',
T4.[ReqDate] as 'Required Date',T5.[ShipDate] as 'Despatch Date',T5.[LineTotal] as 'Row Value'
FROM OWOR T0
INNER JOIN (select Docentry from WOR1 where ItemCode not in ('SUNDRY LABOUR','OUTSOURCED') group by DocEntry) 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.[DocEntry] = T0.[OriginAbs]
LEFT JOIN RDR1 T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode
WHERE
T0.[DueDate] between @Fdate and @Tdate and
T0.[Status] not in ( 'C')
GROUP BY
T0.[DocNum], T0.[DueDate],T0.[U_CustName], T0.[PlannedQty], T0.[ItemCode], T2.[ItemName], T0.[Comments], T3.[ItmsGrpNam], T0.[Status], T4.[ReqDate], T5.[ShipDate],
T5.[LineTotal],T1.DocEntry
ORDER BY
T0.[DueDate], T0.[DocNum]
Hi Rahul
Please note that the rows with duplicate values contains different values for Dispatch Date and Row Value. The group by will work only for rows with same values.
If you want to display sales data as per Row then the query is showing correctly.
Incase you want data in single row then I would suggest either you remove the Dispatch Date and add Row Values or remove both fields.
Hi Gaurav
I removed the Dispatch date but I am getting the same result
So i checked the duplicate results against the sales order
Row 45 & 46
The sales order has two rows, different production orders,quantities and despatch dates but same product code
In the query result the despatch dates and the row value are the only things correct, everything else it shows what is in the last despatch date in that sales order
Same think with row 50 & 51 and all the other duplicates
If the sales order has the same product code in different rows, the query result is shows all the rows but the only thing correct is the despatch dates and row values
See attached
That explains a lot. the sales order contains 2 or more rows per document which links all the rows to same production order. Try the query I converted docnum to nvarchar hope it works.
Declare @FDate datetime
Declare @TDate Datetime
Select @FDate = Min(F1.DueDate) from OWOR F1 where F1.DueDate >= [%0]
Select @TDate = Max(F2.DueDate) from OWOR F2 where F2.DueDate <= [%1]
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',
Isnull((select SUM(S1.[PlannedQty]) from WOR1 S1 where S1.DocEntry = T1.DocEntry and S1.ItemCode = 'LABOUR' GROUP BY S1.DocEntry),0) as 'Time',T0.[Comments] as 'Remarks',
T3.[ItmsGrpNam] as 'Item Group',
case when T0.[Status] = 'P' then 'Planned'
when T0.[Status] = 'R' then 'Released'
when T0.[Status] = 'L' then 'Closed'
when T0.[Status] = 'C' then 'Cancelled' end as 'Prod Status',
T4.[ReqDate] as 'Required Date',T5.[ShipDate] as 'Despatch Date',T5.[LineTotal] as 'Row Value'
FROM OWOR T0
INNER JOIN (select Docentry from WOR1 where ItemCode not in ('SUNDRY LABOUR','OUTSOURCED') group by DocEntry) 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.[DocEntry] = T0.[OriginAbs]
LEFT JOIN RDR1 T5 ON T5.DocEntry = T4.DocEntry AND T5.ItemCode=T0.ItemCode and T5.[U_Prod_Order_No] = convert(nvarchar(50),T0.[DocNum])
WHERE
T0.[DueDate] between @Fdate and @Tdate and
T0.[Status] not in ( 'C')
GROUP BY
T0.[DocNum], T0.[DueDate],T0.[U_CustName], T0.[PlannedQty], T0.[ItemCode], T2.[ItemName], T0.[Comments], T3.[ItmsGrpNam], T0.[Status], T4.[ReqDate], T5.[ShipDate],
T5.[LineTotal],T1.DocEntry
ORDER BY
T0.[DueDate], T0.[DocNum]
Hi Rahul
The link button appears automatically, mostly on primary keys,
Personally I think the link button points to records hence they cannot be on the column which can contain duplicate values.
Mostly theses are Key fields and predefined. There is no way to create or add them to query manager.
User | Count |
---|---|
91 | |
7 | |
7 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.