Ventas menos devoluciones

[andrescamilo1220]

Buenos dias a ver si me echan una mano con este tema, tengo la consulta de las ventas.

Código SQL:

SELECT T0.[ItemCode], T0.[Dscription], SUM(T0.[Quantity]) 
FROM INV1 T0  INNER JOIN OINV T1 ON T0.[DocEntry] = T1.[DocEntry] 
WHERE T1.[DocDate] BETWEEN '01-01-2016' AND '31-01-2016' 
GROUP BY T0.[ItemCode], T0.[Dscription] 
ORDER BY T0.[ItemCode] ASC
 

Y las devoluciones

Código SQL:

SELECT T2.[ItemCode], T2.[Dscription], SUM(T2.[Quantity])
FROM RIN1 T2  INNER JOIN ORIN T3 ON T2.[DocEntry] = T3.[DocEntry] 
WHERE T2.[DocDate] BETWEEN '01-01-2016' AND '31-01-2016'
GROUP BY T2.[ItemCode], T2.[Dscription]
ORDER BY T2.[ItemCode] ASC
 

Pero hasta el momento no e logrado restarlas...

[Thumper]

Que tal, pon datos de ejemplos y el resultado esperado.

Saludos

[ejbsoft]

Iniciado por andrescamilo1220

Buenos dias a ver si me echan una mano con este tema, tengo la consulta de las ventas.

Código SQL:

SELECT T0.[ItemCode], T0.[Dscription], SUM(T0.[Quantity]) 
FROM INV1 T0  INNER JOIN OINV T1 ON T0.[DocEntry] = T1.[DocEntry] 
WHERE T1.[DocDate] BETWEEN '01-01-2016' AND '31-01-2016' 
GROUP BY T0.[ItemCode], T0.[Dscription] 
ORDER BY T0.[ItemCode] ASC
 

Y las devoluciones

Código SQL:

SELECT T2.[ItemCode], T2.[Dscription], SUM(T2.[Quantity])
FROM RIN1 T2  INNER JOIN ORIN T3 ON T2.[DocEntry] = T3.[DocEntry] 
WHERE T2.[DocDate] BETWEEN '01-01-2016' AND '31-01-2016'
GROUP BY T2.[ItemCode], T2.[Dscription]
ORDER BY T2.[ItemCode] ASC
 

Pero hasta el momento no e logrado restarlas...

Hola

Qué tal algo tan simple como esto?

Código SQL:

SELECT T0.[ItemCode], T0.[Dscription], SUM(T0.[Quantity]) 
FROM INV1 T0  INNER JOIN OINV T1 ON T0.[DocEntry] = T1.[DocEntry] 
WHERE T1.[DocDate] BETWEEN '01-01-2016' AND '31-01-2016' 
GROUP BY T0.[ItemCode], T0.[Dscription] 
ORDER BY T0.[ItemCode] ASC
 
UNION ALL
 
SELECT T2.[ItemCode], T2.[Dscription], SUM(T2.[Quantity])
FROM RIN1 T2  INNER JOIN ORIN T3 ON T2.[DocEntry] = T3.[DocEntry] 
WHERE T2.[DocDate] BETWEEN '01-01-2016' AND '31-01-2016'
[I]AND t2.[itemcode] NOT IN (SELECT itemcode FROM inv1 WHERE DocDate BETWEEN '01-01-2016' AND '31-01-2016' ) [/I]
GROUP BY T2.[ItemCode], T2.[Dscription]
ORDER BY T2.[ItemCode] ASC
 

[Libras]

Iniciado por ejbsoft

Hola

Qué tal algo tan simple como esto?

SELECT T0.[ItemCode], T0.[Dscription], sum(T0.[Quantity])
FROM INV1 T0 INNER JOIN OINV T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T1.[DocDate] BETWEEN '01-01-2016' AND '31-01-2016'
GROUP BY T0.[ItemCode], T0.[Dscription]
ORDER BY T0.[ItemCode] ASC

unión all

SELECT T2.[ItemCode], T2.[Dscription], sum(T2.[Quantity])
FROM RIN1 T2 INNER JOIN ORIN T3 ON T2.[DocEntry] = T3.[DocEntry]
WHERE T2.[DocDate] BETWEEN '01-01-2016' AND '31-01-2016'
and t2.[itemcode] not in (select itemcode from inv1 WHERE DocDate BETWEEN '01-01-2016' AND '31-01-2016' )
GROUP BY T2.[ItemCode], T2.[Dscription]
ORDER BY T2.[ItemCode] ASC

Y la resta??? esta preguntando por como hacer la resta de las ventas menos las compras

Código SQL:

SELECT ventas.itemcode,ventas.dscription,  ventas-devoluciones AS resta FROM(
SELECT T0.[ItemCode], T0.[Dscription], SUM(T0.[Quantity]) AS ventas
FROM INV1 T0 INNER JOIN OINV T1 ON T0.[DocEntry] = T1.[DocEntry] 
WHERE T1.[DocDate] BETWEEN '01-01-2016' AND '31-01-2016' 
GROUP BY T0.[ItemCode], T0.[Dscription] ) AS ventas
LEFT JOIN
(
SELECT T2.[ItemCode], T2.[Dscription], SUM(T2.[Quantity]) AS devoluciones
FROM RIN1 T2 INNER JOIN ORIN T3 ON T2.[DocEntry] = T3.[DocEntry] 
WHERE T2.[DocDate] BETWEEN '01-01-2016' AND '31-01-2016'
GROUP BY T2.[ItemCode], T2.[Dscription]
) AS devoluciones ON (ventas.itemcode=devoluciones.itemcode)