views:

416

answers:

3

Hi i was wondering if anyone knows how i can calculate the difference between two tables in tsql. I dont mean finding which cells are different - i mean calulcating the numerical difference. eg - Table A has column1, column 2 and only one 1 row. A1 = 40, B1 = 30. Table B has column1, column 2 and only one 1 row. A1 = 25, B1 = 10. So how could i get (A1 = 15, B1 = 20) using TSQL?

+1  A: 
SELECT  a.column1 - b.column1 , a.column2 - b.column2
FROM    a
CROSS JOIN
        b
Quassnoi
+1  A: 

Free from my mind =)

Select 
(CONVERT(int, T1.A1) - Convert(int, T2.A1)) as A1,
(CONVERT(int, T1.B1) - Convert(int, T2.B)) as B1 
From Table1 T1
inner join Table2 T2 on  T1.Key = T2.Key
Kovu
+2  A: 

Given that you have no way to join the tables, you'll need a Cartesian product of the two. Luckily since each table only has one record that's not a problem.

You do it like this:

SELECT 
  TableA.A1 - TableB.A1 AS A1,
  TableA.B1 - TableB.B1 AS B1
FROM TableA, TableB

If you had more than one record in each table, this query would return a result for every pair of records in both tables. So if TableA had n records and TableB has m, the result will have n*m records.

Welbog