Duplicado de datos

Buena tarde compañeros, tengo una pregunta estoy haciendo una consulta, la cual me traigo la cantidad vendida y hago una unión con otros datos como las notas de crédito gracias a esto me cuadran mis datos en general. Pero al momento de ejecutar se podría decir que me duplica datos. le pongo un ejemplo de como aparece y mi consulta.

SELECT DISTINCT 
T4.[ItemCode] AS 'Número de artículo', 
MAX(T4.Dscription) as 'Descripción', 
'Stock'= (SELECT DISTINCT SUM(S1.OnHand) FROM OITM S0  INNER JOIN OITW S1 ON S0.[ItemCode] = S1.[ItemCode] WHERE S1.[ItemCode] =T4.[ItemCode]),
'Stock Máximo'= (SELECT DISTINCT Max(S1.MaxStock) FROM OITM S0  INNER JOIN OITW S1 ON S0.[ItemCode] = S1.[ItemCode] WHERE S1.[ItemCode] =T4.[ItemCode]),
'Stock Mínimo '= (SELECT DISTINCT Max(S1.MinStock) FROM OITM S0  INNER JOIN OITW S1 ON S0.[ItemCode] = S1.[ItemCode] WHERE S1.[ItemCode] =T4.[ItemCode]),
'Ventas Promedio 6 meses' = ((SELECT COALESCE(SUM(S0.QUANTITY),0) FROM INV1 S0 INNER JOIN OINV S1 ON S0.[DocEntry] = S1.[DocEntry] WHERE  S0.ITEMCODE = T4.ITEMCODE  AND S1.CANCELED='N' AND DATEDIFF(DD, S0.[DocDate]  , GETDATE()) <= 180)/6),
SUM(CASE WHEN Month(T0.[Docdate])=1 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Enero',
SUM(CASE WHEN Month(T0.[Docdate])=2 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Febrero',
SUM(CASE WHEN Month(T0.[Docdate])=3 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Marzo',
SUM(CASE WHEN Month(T0.[Docdate])=4 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Abril',
SUM(CASE WHEN Month(T0.[Docdate])=5 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Mayo',
SUM(CASE WHEN Month(T0.[Docdate])=6 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Junio',
SUM(CASE WHEN Month(T0.[Docdate])=7 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Julio',
SUM(CASE WHEN Month(T0.[Docdate])=8 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Agosto',
SUM(CASE WHEN Month(T0.[Docdate])=9 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Septiembre',
SUM(CASE WHEN Month(T0.[Docdate])=10 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Octubre',
SUM(CASE WHEN Month(T0.[Docdate])=11 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Noviembre',
SUM(CASE WHEN Month(T0.[Docdate])=12 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Diciembre'

FROM OINV T0 
INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode]
INNER JOIN INV1 T4 ON T4.[DocEntry] = T0.[DocEntry]
INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]


WHERE  T0.[CANCELED] ='N' AND T4.[Quantity] <> 0


GROUP BY  T4.[ItemCode]

UNION

SELECT DISTINCT
T4.[ItemCode] AS 'Número de artículo', 
MAX(T4.Dscription) as 'Descripción', 
'Stock'= (SELECT SUM(S1.OnHand) FROM OITM S0  INNER JOIN OITW S1 ON S0.[ItemCode] = S1.[ItemCode] WHERE S1.[ItemCode] =T4.[ItemCode]),
'Stock Máximo'= (SELECT DISTINCT Max(S1.MaxStock) FROM OITM S0  INNER JOIN OITW S1 ON S0.[ItemCode] = S1.[ItemCode] WHERE S1.[ItemCode] =T4.[ItemCode]),
'Stock Mínimo '= (SELECT DISTINCT Max(S1.MinStock) FROM OITM S0  INNER JOIN OITW S1 ON S0.[ItemCode] = S1.[ItemCode] WHERE S1.[ItemCode] =T4.[ItemCode]),
'Ventas Promedio 6 meses' = ((SELECT COALESCE(SUM(S0.QUANTITY),0) FROM INV1 S0 INNER JOIN OINV S1 ON S0.[DocEntry] = S1.[DocEntry] WHERE S0.ITEMCODE = T4.ITEMCODE  AND S1.CANCELED='N' AND DATEDIFF(DD, S0.[DocDate]  , GETDATE()) <= 180)/6),
SUM(CASE WHEN Month(T0.[Docdate])=1 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Enero',
SUM(CASE WHEN Month(T0.[Docdate])=2 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Febrero',
SUM(CASE WHEN Month(T0.[Docdate])=3 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Marzo',
SUM(CASE WHEN Month(T0.[Docdate])=4 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Abril',
SUM(CASE WHEN Month(T0.[Docdate])=5 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Mayo',
SUM(CASE WHEN Month(T0.[Docdate])=6 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Junio',
SUM(CASE WHEN Month(T0.[Docdate])=7 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Julio',
SUM(CASE WHEN Month(T0.[Docdate])=8 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Agosto',
SUM(CASE WHEN Month(T0.[Docdate])=9 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Septiembre',
SUM(CASE WHEN Month(T0.[Docdate])=10 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Octubre',
SUM(CASE WHEN Month(T0.[Docdate])=11 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Noviembre',
SUM(CASE WHEN Month(T0.[Docdate])=12 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Diciembre' 


FROM ORIN T0 
INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
INNER JOIN RIN1 T4 ON T4.DocEntry = T0.DocEntry
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode


WHERE T0.[CANCELED] ='N' AND T4.[Quantity] <> 0


GROUP BY  T4.[ItemCode]

Hola que tal Elizabeth
Utilicé el EXCEPT en vez de UNION, espero te sirva

SELECT DISTINCT 
T4.[ItemCode] AS 'Número de artículo', 
MAX(T4.Dscription) as 'Descripción', 
'Stock'= (SELECT DISTINCT SUM(S1.OnHand) FROM OITM S0  INNER JOIN OITW S1 ON S0.[ItemCode] = S1.[ItemCode] WHERE S1.[ItemCode] =T4.[ItemCode]),
'Stock Máximo'= (SELECT DISTINCT Max(S1.MaxStock) FROM OITM S0  INNER JOIN OITW S1 ON S0.[ItemCode] = S1.[ItemCode] WHERE S1.[ItemCode] =T4.[ItemCode]),
'Stock Mínimo '= (SELECT DISTINCT Max(S1.MinStock) FROM OITM S0  INNER JOIN OITW S1 ON S0.[ItemCode] = S1.[ItemCode] WHERE S1.[ItemCode] =T4.[ItemCode]),
'Ventas Promedio 6 meses' = ((SELECT COALESCE(SUM(S0.QUANTITY),0) FROM INV1 S0 INNER JOIN OINV S1 ON S0.[DocEntry] = S1.[DocEntry] WHERE  S0.ITEMCODE = T4.ITEMCODE  AND S1.CANCELED='N' AND DATEDIFF(DD, S0.[DocDate]  , GETDATE()) <= 180)/6),
SUM(CASE WHEN Month(T0.[Docdate])=1 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Enero',
SUM(CASE WHEN Month(T0.[Docdate])=2 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Febrero',
SUM(CASE WHEN Month(T0.[Docdate])=3 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Marzo',
SUM(CASE WHEN Month(T0.[Docdate])=4 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Abril',
SUM(CASE WHEN Month(T0.[Docdate])=5 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Mayo',
SUM(CASE WHEN Month(T0.[Docdate])=6 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Junio',
SUM(CASE WHEN Month(T0.[Docdate])=7 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Julio',
SUM(CASE WHEN Month(T0.[Docdate])=8 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Agosto',
SUM(CASE WHEN Month(T0.[Docdate])=9 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Septiembre',
SUM(CASE WHEN Month(T0.[Docdate])=10 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Octubre',
SUM(CASE WHEN Month(T0.[Docdate])=11 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Noviembre',
SUM(CASE WHEN Month(T0.[Docdate])=12 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Diciembre'

FROM OINV T0 
INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode]
INNER JOIN INV1 T4 ON T4.[DocEntry] = T0.[DocEntry]
INNER JOIN OSLP T1 ON T0.[SlpCode] = T1.[SlpCode]


WHERE  T0.[CANCELED] ='N' AND T4.[Quantity] <> 0


GROUP BY  T4.[ItemCode]

EXCEPT

SELECT DISTINCT
T4.[ItemCode] AS 'Número de artículo', 
MAX(T4.Dscription) as 'Descripción', 
'Stock'= (SELECT SUM(S1.OnHand) FROM OITM S0  INNER JOIN OITW S1 ON S0.[ItemCode] = S1.[ItemCode] WHERE S1.[ItemCode] =T4.[ItemCode]),
'Stock Máximo'= (SELECT DISTINCT Max(S1.MaxStock) FROM OITM S0  INNER JOIN OITW S1 ON S0.[ItemCode] = S1.[ItemCode] WHERE S1.[ItemCode] =T4.[ItemCode]),
'Stock Mínimo '= (SELECT DISTINCT Max(S1.MinStock) FROM OITM S0  INNER JOIN OITW S1 ON S0.[ItemCode] = S1.[ItemCode] WHERE S1.[ItemCode] =T4.[ItemCode]),
'Ventas Promedio 6 meses' = ((SELECT COALESCE(SUM(S0.QUANTITY),0) FROM INV1 S0 INNER JOIN OINV S1 ON S0.[DocEntry] = S1.[DocEntry] WHERE S0.ITEMCODE = T4.ITEMCODE  AND S1.CANCELED='N' AND DATEDIFF(DD, S0.[DocDate]  , GETDATE()) <= 180)/6),
SUM(CASE WHEN Month(T0.[Docdate])=1 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Enero',
SUM(CASE WHEN Month(T0.[Docdate])=2 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Febrero',
SUM(CASE WHEN Month(T0.[Docdate])=3 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Marzo',
SUM(CASE WHEN Month(T0.[Docdate])=4 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Abril',
SUM(CASE WHEN Month(T0.[Docdate])=5 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Mayo',
SUM(CASE WHEN Month(T0.[Docdate])=6 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Junio',
SUM(CASE WHEN Month(T0.[Docdate])=7 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Julio',
SUM(CASE WHEN Month(T0.[Docdate])=8 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Agosto',
SUM(CASE WHEN Month(T0.[Docdate])=9 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Septiembre',
SUM(CASE WHEN Month(T0.[Docdate])=10 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Octubre',
SUM(CASE WHEN Month(T0.[Docdate])=11 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Noviembre',
SUM(CASE WHEN Month(T0.[Docdate])=12 AND Year(T0.[DocDate]) = 2020 then (T4.Quantity) else 0 END) as 'Diciembre' 


FROM ORIN T0 
INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
INNER JOIN RIN1 T4 ON T4.DocEntry = T0.DocEntry
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode


WHERE T0.[CANCELED] ='N' AND T4.[Quantity] <> 0


GROUP BY  T4.[ItemCode]
order by [Número de artículo]

2 Me gusta

Este tema se cerró automáticamente 7 días después de la última publicación. No se permiten nuevas respuestas.