Hi all,
I'd like to remove duplicates ItemCode rows of my results.
Keep all those from the first query and remove from the second query those who are already in the first. The thing is that some values of other columns aren't the same so the DISTINCT or UNION are not working but I need to keep de mismatch of values of the other columns if i want to keep the maximum good records.
I've attached an example of results. Thanks in advance!!
Here's what I have for now ;
SELECT DISTINCT T1.[ItemCode], T2.[ItemName], SUM(((T1.Quantity) / 365) * 1.15) As 'Average daily use', (T2.[OnHand] - T2.[IsCommited]) As 'Available', (T2.[OnHand] - T2.[IsCommited]) - (T2.[LeadTime] * (SUM(T1.Quantity) / 365) * 1.15) As 'Reste', T2.[LeadTime], MIN(T4.[ShipDate]) As 'Next delivery date', T4.[Quantity], CASE WHEN (T2.[OnHand] - T2.[IsCommited]) - (T2.[LeadTime] * (SUM(T1.Quantity) / 365) * 1.15) > 0 THEN 'OK' WHEN (T2.[OnHand] - T2.[IsCommited]) - (T2.[LeadTime] * (SUM(T1.Quantity) / 365) * 1.15) <= 0 AND (T2.[OnHand] + T4.[Quantity] - T2.[IsCommited]) - (DATEDIFF(DD, GETDATE(), MIN(T4.[ShipDate])) * (SUM(T1.Quantity) / 365) * 1.15) > 0 Then 'Is OK with next delivery' WHEN (T2.[OnHand] - T2.[IsCommited]) - (T2.[LeadTime] * (SUM(T1.Quantity) / 365) * 1.15) <= 0 THEN '***WARNING***' WHEN T2.[LeadTime] IS NULL AND (T2.[OnHand] + T4.[Quantity] - T2.[IsCommited]) - (DATEDIFF(DD, GETDATE(), MIN(T4.[ShipDate])) * (SUM(T1.Quantity) / 365) * 1.15) > 0 THEN 'OK' WHEN T2.[LeadTime] IS NULL AND (T2.[OnHand] + T4.[Quantity] - T2.[IsCommited]) - (DATEDIFF(DD, GETDATE(), MIN(T4.[ShipDate])) * (SUM(T1.Quantity) / 365) * 1.15) <= 0 THEN '***WARNING***' END As 'State', DATEDIFF(DD, GETDATE(), MIN(T4.[ShipDate])) As 'Days until delivery' FROM OIGE T0 INNER JOIN IGE1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T2.[ItemCode] = T1.[ItemCode] INNER JOIN OITB T3 ON T3.[ItmsGrpCod] = T2.[ItmsGrpCod] INNER JOIN POR1 T4 ON T2.[ItemCode] = T4.[ItemCode] WHERE T2.[PrchseItem] = 'Y' AND T2.[validFor] = 'Y' AND T2.[TreeType] <> 'P' AND T0.Docdate >= DATEADD(Year,-1,GETDATE()) AND LEFT(T1.[ItemCode],2) <> 'O-' AND T4.[ShipDate] >= GETDATE() GROUP BY T1.[ItemCode], T2.[ItemName],T2.[OnHand],T2.[IsCommited],T2.[LeadTime],T4.[Quantity], T2.[OnOrder] UNION ALL SELECT DISTINCT T1.[ItemCode], T2.[ItemName], SUM(((T1.Quantity) / 365) * 1.15) As 'Average daily use', (T2.[OnHand] - T2.[IsCommited]) As 'Available', (T2.[OnHand] - T2.[IsCommited]) - (T2.[LeadTime] * (SUM(T1.Quantity) / 365) * 1.15) As 'Reste', T2.[LeadTime], NULL, NULL, CASE WHEN (T2.[OnHand] - T2.[IsCommited]) - (T2.[LeadTime] * (SUM(T1.Quantity) / 365) * 1.15) > 0 THEN 'OK' WHEN (T2.[OnHand] - T2.[IsCommited]) - (T2.[LeadTime] * (SUM(T1.Quantity) / 365) * 1.15) <= 0 THEN '***WARNING***' WHEN T2.[LeadTime] IS NULL THEN '------------' END As 'State', '' FROM OIGE T0 INNER JOIN IGE1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T2.[ItemCode] = T1.[ItemCode] INNER JOIN OITB T3 ON T3.[ItmsGrpCod] = T2.[ItmsGrpCod] WHERE T2.[PrchseItem] = 'Y' AND T2.[validFor] = 'Y' AND T2.[TreeType] <> 'P' AND T0.Docdate >= DATEADD(Year,-1,GETDATE()) AND LEFT(T1.[ItemCode],2) <> 'O-' GROUP BY T1.[ItemCode], T2.[ItemName],T2.[OnHand],T2.[IsCommited],T2.[LeadTime],T2.[OnOrder] ORDER BY T1.[ItemCode]t21.jpg