on 09-28-2012 2:55 PM
Hola saludos a todos que necesito de su ayuda, tengo 2 querys que me traen los mismos datos pero son de 2 tablas diferentes INV1 y DLN1 y son los sig
SELECT
T0.[ItemCode], T1.ItemName, T1.InvntryUom, T1.U_FAMALM AS Familia,SUM(Quantity) AS Cantidad
FROM
DLN1 T0
INNER JOIN OITM T1 ON T1.ItemCode = T0.ItemCode
INNER JOIN OITB T2 ON T2.ItmsGrpCod = T1.ItmsGrpCod
WHERE
T0.ActDelDate >='20120101' AND T0.ActDelDate <= '20120115'
AND T0.TargetType NOT LIKE '16'
GROUP BY
T0.[ItemCode], T1.ItemName, T1.InvntryUom, T1.U_FAMALM
Y
select
T0.ItemCode, T1.ItemName,T1.InvntryUom, T1.U_FAMALM AS Familia, SUM(T0.Quantity) AS Cantidad
from
INV1 T0 INNER JOIN OITM T1 ON T1.ItemCode = T0.ItemCode
INNER JOIN OITB T2 ON T2.ItmsGrpCod = T1.ItmsGrpCod
--INNER JOIN OINV T2 ON T2.DocEntry = T0.DocEntry
WHERE
T0.ActDelDate >='20120101' AND T0.ActDelDate <= '20120115'
AND T0.BaseType NOT LIKE '15'
AND T0.TargetType NOT LIKE '14'
GROUP BY
T0.ItemCode, T1.ItemName, T1.InvntryUom, T1.U_FAMALM
Lo que necesito hacer es que unirlos para que me sume las cantidades que dan en cada uno de los artículos . Espero puedan ayudarme gracias.
Saludos!!!
Prueba esto:
SELECT T0.ItemCode, T0.ItemName, T0.InvtryUom, T0.Familia, SUM(T0.Cantidad)
FROM (
SELECT
T0.[ItemCode] AS ItemCode,
T1.ItemName AS ItemName,
T1.InvntryUom AS InvtryUom,
T1.U_FAMALM AS Familia,
SUM(Quantity) AS Cantidad
FROM
dbo.DLN1 T0
INNER JOIN dbo.OITM T1 ON T1.ItemCode = T0.ItemCode
INNER JOIN dbo.OITB T2 ON T2.ItmsGrpCod = T1.ItmsGrpCod
WHERE
T0.ActDelDate >='[%0]' AND T0.ActDelDate <= '[%1]'
AND T0.TargetType NOT LIKE '16'
GROUP BY
T0.[ItemCode], T1.ItemName, T1.InvntryUom, T1.U_FAMALM
UNION ALL
SELECT
T0.[ItemCode] AS ItemCode,
T1.ItemName AS ItemName,
T1.InvntryUom AS InvtryUom,
T1.U_FAMALM AS Familia,
SUM(Quantity) AS Cantidad
FROM
dbo.INV1 T0
INNER JOIN dbo.OITM T1 ON T1.ItemCode = T0.ItemCode
INNER JOIN dbo.OITB T2 ON T2.ItmsGrpCod = T1.ItmsGrpCod
WHERE
T0.ActDelDate >='[%0]' AND T0.ActDelDate <= '[%1]'
AND T0.BaseType NOT LIKE '15'
AND T0.TargetType NOT LIKE '14'
GROUP BY
T0.ItemCode, T1.ItemName, T1.InvntryUom, T1.U_FAMALM
) T0
GROUP BY T0.ItemCode, T0.ItemName, T0.InvtryUom, T0.Familia
Saludos
Felipe
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hola Orlando
Revisa este query, de repente te puede servir
/* SELECT FROM [DBO.INV1] X1 */
DECLARE @Desde AS datetime
/*WHERE*/ SET
@Desde= '20120101'--/* X1.ActDelDate */ '[%0]'
/* SELECT FROM [DBO.INV1] X1 */
DECLARE @Hasta AS datetime /*WHERE*/
SET @Hasta= '20120115'--/* X1.ActDelDate */ '[%1]'
SELECT
x0.itemcode [Articulo], x0.ItemName [Descripcion], CASE WHEN x0.InvntryUom IS NULL THEN 'Sin Asignar' ELSE x0.InvntryUom END [U.Medida Inv.],
CASE
WHEN SUM(X1.QtyFactura) IS NULL THEN 0 ELSE SUM(X1.QtyFactura) END [Cant.Factura],
CASE
WHEN sum(x2.QtyEntrega) IS NULL THEN 0 ELSE sum(x2.QtyEntrega) END [Cant.Entrega]
FROM
(
SELECT itemcode, itemname,InvntryUom FROM OITM ) AS X0
inner join
(
select itemcode,(quantity) [QtyFactura],ActDelDate
from INV1
where BaseType NOT LIKE '15' AND TargetType NOT LIKE '14' AND ActDelDate >= @Desde AND ActDelDate <= @Hasta
group by ItemCode, ActDelDate, quantity
) x1 on x0.ItemCode = x1.ItemCode
LEFT join
(
SELECT itemcode,(quantity) [QtyEntrega],ActDelDate
FROM DLN1
WHERE TargetType NOT LIKE '16' AND ActDelDate >= @Desde AND ActDelDate <= @Hasta
group by ItemCode, quantity,ActDelDate
) X2 ON X0.ItemCode = X2.ItemCode
gROUP BY x0.ItemCode, x0.ItemName, x0.InvntryUom
order by X0.ItemCode
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hola Orlando, te modifique la consulta no habia sumado las dos columnas de cantidad
Espero te funcione. Saludos
/* SELECT FROM [DBO.INV1] X1 */
DECLARE
@Desde AS datetime
/*WHERE*/
SET
@Desde= '20120101'--/* X1.ActDelDate */ '[%0]'
/* SELECT FROM [DBO.INV1] X1 */
DECLARE
@Hasta AS datetime /*WHERE*/
SET
@Hasta= '20120115'--/* X1.ActDelDate */ '[%1]'
SELECT
x0.itemcode [Articulo], x0.ItemName [Descripcion], CASE WHEN x0.InvntryUom IS NULL THEN 'Sin Asignar' ELSE x0.InvntryUom END [U.Medida Inv.], X0.Familia,
sum
(case when x1.QtyFactura is null then 0 else x1.QtyFactura end+case when X2.QtyEntrega IS null then 0 else X2.QtyEntrega end) [Cantidad]
FROM
(
SELECT itemcode, itemname,InvntryUom,U_FAMALM [Familia] FROM OITM ) AS X0
inner join
(
select itemcode,(quantity) [QtyFactura],ActDelDate
from INV1
where BaseType NOT LIKE '15' AND TargetType NOT LIKE '14' AND ActDelDate >= @Desde AND ActDelDate <= @Hasta
group by ItemCode, ActDelDate, quantity
) x1 on x0.ItemCode = x1.ItemCode
LEFT
join
(
SELECT itemcode,(quantity)[QtyEntrega],ActDelDate
FROM DLN1
WHERE TargetType NOT LIKE '16' AND ActDelDate >= @Desde AND ActDelDate <= @Hasta
group by ItemCode, ActDelDate, Quantity
) X2 ON X0.ItemCode = X2.ItemCode
gROUP
BY x0.ItemCode, x0.ItemName, x0.InvntryUom,X0.Familia
order
by X0.ItemCode
Utiliza la función Union all así:
Query 1
Union all
Query 2
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
si eso es lo que necesito y ya pude con el siguiente query pero ahora si no ha articulo e la factura me pune el campo nulo intente con un case pero sigue igual
SELECT
CASE
WHEN T0.ItemCode IN (SELECT ItemCode FROM INV1 ) THEN SUM(T0.Quantity) + (SELECT SUM(T1.Quantity) FROM INV1 T1
WHERE T1.ItemCode = T0.Itemcode AND
T1.BaseType NOT LIKE '15' AND T1.TargetType NOT LIKE '14'
AND T1.ActDelDate >= '20120101' AND T1.ActDelDate <= '20120115' ) ELSE SUM(ISNULL(T0.quantity,0))
END AS Cantidad
,T0.ItemCode, T2.ItemName, T2.InvntryUom, T2.U_FAMALM
FROM
DLN1 T0
INNER JOIN OITM T2 ON T2.ItemCode = T0.ItemCode
INNER JOIN OITB T3 ON T3.ItmsGrpCod = T2.ItmsGrpCod
WHERE
T0.TargetType NOT LIKE '16'
AND
T0.ActDelDate >='20120101' AND T0.ActDelDate <= '20120115''
AND
T3.ItmsGrpNam = 'ALIMENTOS'
GROUP BY
T0.ItemCode, T2.ItemName, T2.InvntryUom,T2.U_FAMALM
order by t0.ItemCode
User | Count |
---|---|
91 | |
10 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.