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
2009-07-09 12:34:01
+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
2009-07-09 12:34:37
+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
2009-07-09 12:35:28