Skip to Content
0
Jul 21, 2020 at 04:15 PM

Duplicates in Query

50 Views Last edit Jul 21, 2020 at 04:16 PM 2 rev

Created a query based on what was asked for. It has duplicates I believe because of both warehouses. My Boss wants the items to be on the same line wants the quanities to be the sum of both warehouses. Is this possible ?

SELECT T2.ItemCode, T3.ItmsGrpNam,T2.ItemName, 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' ,T2.OnOrder AS 'On PO' ,(T2.OnHand - T2.IsCommited + T2.OnOrder) AS 'Available' ,T4.MinStock AS 'Minimum Qty' ,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,T2.IsCommited,T2.OnOrder,T4.OnHand,T4.IsCommited,T4.OnOrder,T4.MinStock

Attachments

querycapture.png (131.0 kB)