Hi Try Below query
DECLARE @IncommingT1 AS TABLE (C1 VARCHAR(64), Data int)
DECLARE @OutGoingT2 AS TABLE (C1 VARCHAR(64), Data int)
INSERT INTO @IncommingT1
(
C1,
Data
)
VALUES
( 'product1', -- C1 - varchar(64)
5 -- Data - int
),( 'product2', -- C1 - varchar(64)
3 -- Data - int
),( 'product2', -- C1 - varchar(64)
4 -- Data - int
),( 'product1', -- C1 - varchar(64)
3 -- Data - int
);
INSERT INTO @OutGoingT2
(
C1,
Data
)
VALUES
( 'product1', -- C1 - varchar(64)
3 -- Data - int
),( 'product2', -- C1 - varchar(64)
3 -- Data - int
),( 'product1', -- C1 - varchar(64)
4 -- Data - int
),( 'product2', -- C1 - varchar(64)
2 -- Data - int
)
SELECT it.C1,(SUM(DISTINCT it.Data)) - (SUM(DISTINCT ogt.Data)) AS C2 FROM @IncommingT1 AS it
LEFT JOIN @OutGoingT2 AS ogt ON ogt.C1 = it.C1
GROUP BY it.C1,ogt.c1