Union y resta de dos querys

[jcelizondo14]

Que tal buen día resulta que tengo dos querys diferentes pero con los mismos campos solo son diferentes tablas y necesito unirlos y que me realice la resta y me aparezca en el resultado hasta el momento ya los uni con UNION sin problema pero no encuentro como realizar la resta de los valores
1°
SELECT T3.[ItemCode], T3.[Dscription], T2.[CardCode], T2.[CardName], Cantidad=sum(T3.[Quantity]), T3.[unitMsr], Total=sum(T3.[TotalSumSy]) FROM [dbo].[OINV] T2 INNER JOIN [dbo].[INV1] T3 ON T2.DocEntry = T3.DocEntry WHERE T2.[DocType]<>'S' and T2.[DocDate] >= '[%0]' and T2.[DocDate] <= '[%1]' GROUP BY T3.[ItemCode], T3.[Dscription], T2.[CardCode], T2.[CardName], T3.[Quantity], T3.[unitMsr], T3.[TotalSumSy] ORDER BY T3.[ItemCode]
2°
SELECT T1.[ItemCode], T1.[Dscription], T0.[CardCode], T0.[CardName], Cantidad=sum(T1.[Quantity]), T1.[unitMsr], Total=sum(T1.[TotalSumSy]) FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocType]<>'S' and T0.[DocDate]>='[%0]' and T0.[DocDate]<='[%1]' GROUP BY T1.[ItemCode], T1.[Dscription], T0.[CardCode], T0.[CardName], T1.[Quantity], T1.[unitMsr], T1.[TotalSumSy] ORDER BY T1.[ItemCode]

Ojala alguien me pueda ayudar
Gracias

[Libras]

Código SQL:

SELECT campo1-campo2 AS campo_resta FROM(
 
SELECT T3.[ItemCode], T3.[Dscription], T2.[CardCode], T2.[CardName], Cantidad=SUM(T3.[Quantity]), T3.[unitMsr], Total=SUM(T3.[TotalSumSy]) FROM [dbo].[OINV] T2 INNER JOIN [dbo].[INV1] T3 ON T2.DocEntry = T3.DocEntry WHERE T2.[DocType]<>'S' AND T2.[DocDate] >= '[%0]' AND T2.[DocDate] <= '[%1]' GROUP BY T3.[ItemCode], T3.[Dscription], T2.[CardCode], T2.[CardName], T3.[Quantity], T3.[unitMsr], T3.[TotalSumSy] ORDER BY T3.[ItemCode]
UNION
SELECT T1.[ItemCode], T1.[Dscription], T0.[CardCode], T0.[CardName], Cantidad=SUM(T1.[Quantity]), T1.[unitMsr], Total=SUM(T1.[TotalSumSy]) FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocType]<>'S' AND T0.[DocDate]>='[%0]' AND T0.[DocDate]<='[%1]' GROUP BY T1.[ItemCode], T1.[Dscription], T0.[CardCode], T0.[CardName], T1.[Quantity], T1.[unitMsr], T1.[TotalSumSy] ORDER BY T1.[ItemCode] 
) tabla
 

algo asi :)

saludos!

[Andres95]

Asumiendo que se trata de una diferencia de inventario vs salidas y que el campo llave es ItemCode

Lo podrias realizar con un full join con el cual aun cuando no exista inventario te aparecerian las salidas de productos.

Otra opcion seria un LEFT JOIN, pero ya dependeria del escenario que desees contemplar..

En esta liga se explica brevemente cada tipo de outer join.

Usar combinaciones externas

Código:

SELECT ISNULL(S.[itemcode], P.[itemcode]) [itemcode]
       ,ISNULL(S.[dscription], P.[dscription]) [dscription]
       ,ISNULL(S.Cantidad, 0) - ISNULL(P.Cantidad, 0) Cantidad
       ,ISNULL(S.Total, 0) - ISNULL(P.Total, 0) Total
FROM   (
	SELECT T3.[itemcode], 
		   T3.[dscription], 
		   T2.[cardcode], 
		   T2.[cardname], 
		   Cantidad=Sum(T3.[quantity]), 
		   T3.[unitmsr], 
		   Total=Sum(T3.[totalsumsy]) 
	FROM   [dbo].[oinv] T2 
		   INNER JOIN [dbo].[inv1] T3 
				   ON T2.docentry = T3.docentry 
	WHERE  T2.[doctype] <> 'S' 
		   AND T2.[docdate] >= '[%0]' 
		   AND T2.[docdate] <= '[%1]' 
	GROUP  BY T3.[itemcode], 
			  T3.[dscription], 
			  T2.[cardcode], 
			  T2.[cardname], 
			  T3.[quantity], 
			  T3.[unitmsr], 
			  T3.[totalsumsy] 
) S
FULL JOIN (

	SELECT T1.[itemcode], 
		   T1.[dscription], 
		   T0.[cardcode], 
		   T0.[cardname], 
		   Cantidad=Sum(T1.[quantity]), 
		   T1.[unitmsr], 
		   Total=Sum(T1.[totalsumsy]) 
	FROM   orin T0 
		   INNER JOIN rin1 T1 
				   ON T0.docentry = T1.docentry 
	WHERE  T0.[doctype] <> 'S' 
		   AND T0.[docdate] >= '[%0]' 
		   AND T0.[docdate] <= '[%1]' 
	GROUP  BY T1.[itemcode], 
			  T1.[dscription], 
			  T0.[cardcode], 
			  T0.[cardname], 
			  T1.[quantity], 
			  T1.[unitmsr], 
			  T1.[totalsumsy] 
) P 
ON S.[itemcode] = p.[itemcode]
ORDER BY ISNULL(S.[itemcode], P.[itemcode])

Saludos!