December 3, 2018 at 8:12 pm
TABLE A
year title country age
2018 MR UK 50
2018 MS UK 40
TABLE B
year title country age
2018 MR UK 10
2018 MS UK 20
2018 Sir UK 20
How can i subtract this 2 tables? if both not match, just show as it is.
Expected Result
year title country diff
2018 MR UK 40
2018 MS UK 20
2018 Sir UK 20
December 3, 2018 at 8:36 pm
This is close...
CREATE TABLE #tblA (
Yr INT
,Title CHAR(3)
,Country CHAR(2)
,Age TINYINT
);
CREATE TABLE #tblB (
Yr INT
,Title CHAR(3)
,Country CHAR(2)
,Age TINYINT
);
GO
INSERT INTO #tblA
VALUES (2018, 'MR', 'UK', 50) ,(2018, 'MS', 'UK', 40);
INSERT INTO #tblB
VALUES (2018, 'MR', 'UK', 10), (2018, 'MS', 'UK', 20), (2018, 'Sir', 'UK', 20);
SELECT d.Yr, d.Title, d.Country, SUM(d.Age)
FROM
(SELECT a.Yr, a.Title, a.Country, a.Age
FROM #tblA a
UNION ALL
SELECT b.Yr, b.Title, b.Country, -1 * b.Age
FROM #tblB b) d
GROUP BY d.Yr, d.Title, d.Country;
(I get 40, 20, -20)...
December 3, 2018 at 9:22 pm
girl_bj - Monday, December 3, 2018 8:12 PMTABLE A
year title country age
2018 MR UK 50
2018 MS UK 40TABLE B
year title country age
2018 MR UK 10
2018 MS UK 20
2018 Sir UK 20How can i subtract this 2 tables? if both not match, just show as it is.
Expected Result
year title country diff
2018 MR UK 40
2018 MS UK 20
2018 Sir UK 20
Can you try this and to eliminate negative values in age you can add more conditions.
SELECT
b.yr,b.title,b.country,
CASE WHEN a.yr IS NOT NULL
THEN (a.age-b.age) ELSE b.age END AS age
FROM
#tblb B LEFT OUTER JOIN #tbla a
ON (a.yr=b.yr
AND a.title=b.title AND a.country=b.country)
Saravanan
December 3, 2018 at 10:24 pm
Is working for both way. Understand method of joining now. Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply