RE: Subtracting Value – SQLServerCentral

SSC Guru

Points: 1003859

September 7, 2012 at 6:47 pm

Dwain,

You made the same mistake that I originally made. Try your code on the following test data and marvel at the pain of an accidental CROSS JOIN.

DECLARE @table1 TABLE (id INT, a INT)

DECLARE @table2 TABLE (id INT, b INT)

INSERT INTO @table1

SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL

SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9

INSERT INTO @Table1 SELECT * FROM @Table1

INSERT INTO @table2

SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL

SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4

INSERT INTO @Table2 SELECT * FROM @Table2

The problem is when you have more than 1 of the same ID. The t1.ID=t2.ID makes a many-to-many join (CROSS JOIN) between identical ID's.

To fix the problem, we have to calculate the averages first and then do a join on the results. Here's one way...

WITH

cteT1 AS (SELECT ID, T1Avg = AVG(a) FROM @Table1 GROUP BY ID),

cteT2 AS (SELECT ID, T2Avg = AVG(b) FROM @Table2 GROUP BY ID)

SELECT ID = ISNULL(t1.ID,t2.ID),

T1MinusT2 = ISNULL(t1.T1Avg,0) - ISNULL(t2.T2Avg,0)

FROM cteT1 t1

FULL JOIN cteT2 t2

ON t1.ID = t2.ID

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)