Ayuda union de dos consultas

[Jcedillo]

buen dia.

tengo dos consultas (adjunto código) el resultado que me arroja lo manda en dos líneas diferentes y necesito sea sobre la misma linea, adjunto también imagen del resultado. de antemano gracias

SELECT 
T1.[ItemCode] AS ARTICULO,
 (sum(T1.[Quantity])) AS CANTIDAD, 
(sum(T1.[DelivrdQty])) AS CANTENTREGADA,
 (sum(T1.[OpenCreQty])) AS SALDO,
 0 AS STOCK ,
T6.[MinLevel] AS MINIMO

FROM 
ORDR T0 
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode 
INNER JOIN OITW T4 ON T1.[ItemCode]=T4.[ItemCode] 
INNER JOIN OCRD T5 ON T0.[CardCode] = T5.[CardCode]  
INNER JOIN OITM T6 ON T1.[ItemCode] = T6.[ItemCode]
WHERE T1.[LineStatus] = 'O' AND T1.[WhsCode]=T4.[WhsCode]
group by T1.[ItemCode], 
T6.[MinLevel]

UNION ALL


SELECT 
 TX.[ITEMCODE] AS ARTICULO,
0 AS CANTIDAD,
 0 AS CANTENTREGADA, 
0 AS SALDO, 
STOCK= SUM(TX.[OnHand]) ,
0 AS MINIMO

 FROM OITW TX WHERE  TX.[WhsCode] in ('01','08','AT') GROUP BY TX.[ITEMCODE]

[eduardo_buitr]

Muy buen día @Jcedillo prueba este

SELECT 
T1.[ItemCode] AS ARTICULO,
 (sum(T1.[Quantity])) AS CANTIDAD, 
(sum(T1.[DelivrdQty])) AS CANTENTREGADA,
 (sum(T1.[OpenCreQty])) AS SALDO,
 SUM(ISNULL(TY.STOCK,0)) AS STOCK ,
T6.[MinLevel] AS MINIMO

FROM 
ORDR T0 
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode 
INNER JOIN OITW T4 ON T1.[ItemCode]=T4.[ItemCode] 
INNER JOIN OCRD T5 ON T0.[CardCode] = T5.[CardCode]  
INNER JOIN OITM T6 ON T1.[ItemCode] = T6.[ItemCode]
LEFT JOIN (
SELECT 
 TX.[ITEMCODE] AS ARTICULO,
0 AS CANTIDAD,
 0 AS CANTENTREGADA, 
0 AS SALDO, 
STOCK = SUM(TX.[OnHand]) ,
0 AS MINIMO
 FROM OITW TX WHERE  TX.[WhsCode] in ('01','08','AT') GROUP BY TX.[ITEMCODE]
) TY ON TY.ARTICULO = T6.ItemCode
WHERE T1.[LineStatus] = 'O' AND T1.[WhsCode]=T4.[WhsCode]
group by T1.[ItemCode], 
T6.[MinLevel]

Espero te sirva para lo que quieres.

[Jcedillo]

@eduardo_buitr

SELECT
T1.[ItemCode] AS ARTICULO,
(sum(T1.[Quantity])) AS CANTIDAD,
(sum(T1.[DelivrdQty])) AS CANTENTREGADA,
(sum(T1.[OpenCreQty])) AS SALDO,
SUM(ISNULL(TY.STOCK,0)) AS STOCK ,
T6.[MinLevel] AS MINIMO

FROM
ORDR T0
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
INNER JOIN OITW T4 ON T1.[ItemCode]=T4.[ItemCode]
INNER JOIN OCRD T5 ON T0.[CardCode] = T5.[CardCode]
INNER JOIN OITM T6 ON T1.[ItemCode] = T6.[ItemCode]
LEFT JOIN (
SELECT
TX.[ITEMCODE] AS ARTICULO,
0 AS CANTIDAD,
0 AS CANTENTREGADA,
0 AS SALDO,
STOCK = SUM(TX.[OnHand]) ,
0 AS MINIMO
FROM OITW TX WHERE TX.[WhsCode] in (‘01’,‘08’,‘AT’) GROUP BY TX.[ITEMCODE]
) TY ON TY.ARTICULO = T6.ItemCode
WHERE T1.[LineStatus] = ‘O’ AND T1.[WhsCode]=T4.[WhsCode]
group by T1.[ItemCode],
T6.[MinLevel]

gracias, pero no me sirve ya que me trae una cantidad incorrecta, debería ser 65000 y me trae 543699, repite el stock y lo suma de cuantas líneas de pedido tengo (8 pedidos abiertos)

[Jcedillo]

buen día, ya se resolvió lo que necesitaba con el siguiente código. gracias.

SELECT 
T1.[ItemCode] AS ARTICULO,
(sum(T1.[Quantity])) AS CANTIDAD, 
(sum(T1.[DelivrdQty])) AS CANTENTREGADA,
(sum(T1.[OpenCreQty])) AS SALDO,
STOCK=(SELECT SUM(TX.[OnHand]) FROM OITW TX WHERE  TX.[WhsCode] in ('01','08','AT') AND TX.[ItemCode] = T1.[ItemCode]) ,
T6.[MinLevel] AS MINIMO


FROM 
ORDR T0 
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode 
INNER JOIN OITW T4 ON T1.[ItemCode]=T4.[ItemCode] 
INNER JOIN OCRD T5 ON T0.[CardCode] = T5.[CardCode]  
INNER JOIN OITM T6 ON T1.[ItemCode] = T6.[ItemCode]
WHERE T1.[LineStatus] = 'O' AND T1.[WhsCode]=T4.[WhsCode] AND T1.[ShipDate]<=[%2]
group by T1.[ItemCode], 
T6.[MinLevel]