Age16_20 AgeBelow_16 Age21_30 Age31_40 Age41_50 Age51_60 QID
93 81 55 46 54 48 1
13 11 15 16 14 18 2
I want to subtract second row from first? which is best way to do inside stored procedure. performance main issue here so please provide optimal solution.
SELECT
[qid1].AgeBelow16 - [qid2].AgeBelow16 AS [AgeBelow16],
[qid1].Age16_20 - [qid2].Age16_20 AS [Age16_20],
[qid1].Age21_30 - [qid2].Age21_30 AS [Age21_30],
[qid1].Age31_40 - [qid2].Age31_40 AS [Age31_40],
[qid1].Age41_50 - [qid2].Age41_50 AS [Age41_50],
[qid1].Age51_60 - [qid2].Age51_60 AS [Age51_60]
FROM
MyTable AS [qid1]
INNER JOIN
MyTable AS [qid2]
ON [qid1].QID = [qid2].QID - 1
WHERE
[qid1].QID = 1
If possible, however, you would be much better off storing the QID2 values as negatives. That way you don't need to know which one to subtract from the other; it's just a straight SUM.
SELECT
SUM(AgeBelow16) AS [AgeBelow16], -- (93) + (-13) = 80
SUM(Age16_20) AS [Age16_20], -- (81) + (-11) = 70
SUM(Age21_30) AS [Age21_30], -- (55) + (-15) = 40
SUM(Age31_40) AS [Age31_40], -- (46) + (-16) = 30
SUM(Age41_50) AS [Age41_50], -- (54) + (-14) = 40
SUM(Age51_60) AS [Age51_60] -- (48) + (-18) = 30
FROM
MyTable
Assuming you have a unique identifier on the rows (we'll presume it is ID) you could do something like (I have only done the first column, for brevity):
SELECT SUM(Age16_20) FROM
(SELECT Age16_20
FROM table
WHERE ID = 1
UNION ALL
SELECT -Age16_20
FROM table
WHERE ID = 2) Temp
Assumes only 2 rows.
SELECT
SUM(CASE WHEN ID = 2 THEN -Age16_20 ELSE Age16_20 END),
SUM(CASE WHEN ID = 2 THEN -AgeBelow_16 ELSE AgeBelow_16 END),
SUM(CASE WHEN ID = 2 THEN -Age21_30 ELSE Age21_30 END),
...etc
FROM
Mytable
If more then 2 rows (why?) then change the CASE to
CASE ID WHEN 1 THEN Age16_20 WHEN 2 THEN -Age16_20 ELSE NULL END
