SAP B1 Query Duplicate issue

[amswain]

We have a query that is showing duplicates becuase of the T0.[Status] = R and the T0.[Status] Planned.

is there a way to nest the 2 so that we will not have duplicates ?

this is the query - we are getting one line for released and one line for Planned for each time.

SELECT Distinct T2.ItemCode, T3.ItmsGrpNam,T2.ItemName, (T4.OnHand - T4.IsCommited + T4.OnOrder) AS 'Available', T4.OnOrder AS 'ON PO', T4.MinStock, sum(case when T0.[Status] ='R' then (isnull(T1.PlannedQty,0)) else 0 End) as 'Released' ,T2.OnHand , sum(case when T0.[Status] ='P' then (isnull(T1.PlannedQty,0)) else 0 End) as 'Planned' , T2.OnHand - sum(case when T0.[Status] ='P' then (isnull(T1.PlannedQty,0)) else 0 End) AS 'Stock for Planned' ,case when (T4.OnHand - T4.IsCommited+ T4.OnOrder ) < T4.MinStock Then 'Order' else ' ' End AS 'Status' FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry] LEFT OUTER JOIN OITM T2 ON T1.ItemCode=T2.ItemCode LEFT OUTER JOIN OITB T3 ON T2.ItmsGrpCod=T3.ItmsGrpCod LEFT OUTER JOIN OITW T4 ON T4.[ItemCode] = T1.[ItemCode] WHERE T0.[Status] <>'L' and T0.[Status]<>'C' and T1.ItemCode Like '1%' and T4.WhsCode in ('02') Group by T2.ItemCode, T3.ItmsGrpNam,T2.ItemName,T1.PlannedQty, T2.OnHand,T0.Status, T4.MinStock, (T4.OnHand - T4.IsCommited + T4.OnOrder), T4.OnOrder

[Johan_Hakkesteegt · Accepted solution]

Hi Anthony,

If you mean that you only need one of the two, you could just change T0.[Status]<>'C' to T0.[Status]='P' or T0.[Status]='R'.

If you need both lines, you need to decide if you want their values added up, or say the average of the two. After that you just need to SUM or AVG all numeric columns, and obviously not group by these same columns.

Also, when you use GROUP BY, there is no need to use DISTINCT.

Regards,

Johan

[amswain]

Johan,

Thank you for that info. Very helpful. What the Boss is wanting is for the released and the planned to be on the same line. you willl see from the results I am getting a line for released and line for planned for each item she wants the lines combined. Is this even possible ?