Here you can find the differences between two columns from two different tables
Table t1
id price
1 12
2 10
3 18
Table t2
id price
1 22
2 6
3 18
o/p:
itemid priceint1 priceint2 diff
1 12 22 -10
2 10 6 4
3 18 18 0
ABS It is mathematical function that returns the absolute (positive) value of the specified numeric expression.
select coalesce(t1.id,t2.id) as itemid ,t1.price as priceint1,t2.price as priceint2,
abs(coalesce(t1.price,0)-coalesce(t2.price,0)) as [diff]
from t1 full join t2
on t1.id=t2.id
If you want with Negative values remove abs function from the query..
select coalesce(t1.id,t2.id) as itemid ,t1.price as priceint1,t2.price as priceint2,
(coalesce(t1.price,0)-coalesce(t2.price,0)) as [diff]
from t1 full join t2
on t1.id=t2.id
2nd Way
DECLARE @T1 TABLE (Marks CHAR(2), Price SMALLINT);
DECLARE @T2 TABLE (Marks CHAR(2), Price SMALLINT);
INSERT INTO @T1 VALUES('A1',10);
INSERT INTO @T1 VALUES('A2',14);
INSERT INTO @T1 VALUES('A3',21);
INSERT INTO @T2 VALUES('A1',12);
INSERT INTO @T2 VALUES('A2',10);
INSERT INTO @T2 VALUES('A3',24);
SELECT t1.Marks AS ItemID, t1.Price AS PriceInT1
,ABS(t1.Price - t2.Price) AS Diff, t2.Price AS PriceInT2
FROM @T1 AS t1
INNER JOIN @T2 AS t2
ON t1.Marks = t2.Marks
Table t1
id price
1 12
2 10
3 18
Table t2
id price
1 22
2 6
3 18
o/p:
itemid priceint1 priceint2 diff
1 12 22 -10
2 10 6 4
3 18 18 0
ABS It is mathematical function that returns the absolute (positive) value of the specified numeric expression.
select coalesce(t1.id,t2.id) as itemid ,t1.price as priceint1,t2.price as priceint2,
abs(coalesce(t1.price,0)-coalesce(t2.price,0)) as [diff]
from t1 full join t2
on t1.id=t2.id
If you want with Negative values remove abs function from the query..
select coalesce(t1.id,t2.id) as itemid ,t1.price as priceint1,t2.price as priceint2,
(coalesce(t1.price,0)-coalesce(t2.price,0)) as [diff]
from t1 full join t2
on t1.id=t2.id
DECLARE @T1 TABLE (Marks CHAR(2), Price SMALLINT);
DECLARE @T2 TABLE (Marks CHAR(2), Price SMALLINT);
INSERT INTO @T1 VALUES('A1',10);
INSERT INTO @T1 VALUES('A2',14);
INSERT INTO @T1 VALUES('A3',21);
INSERT INTO @T2 VALUES('A1',12);
INSERT INTO @T2 VALUES('A2',10);
INSERT INTO @T2 VALUES('A3',24);
SELECT t1.Marks AS ItemID, t1.Price AS PriceInT1
,ABS(t1.Price - t2.Price) AS Diff, t2.Price AS PriceInT2
FROM @T1 AS t1
INNER JOIN @T2 AS t2
ON t1.Marks = t2.Marks
No comments:
Post a Comment