Last post Jan 28, 2015 08:51 AM by Zhi Lv - MSFT
Apr 22, 2014 11:39 PM|ittr006|LINK
I trying to calculate difference between 2 rows, e/g:
Row1: 1, "Brazil", 3, 4, 5
Row2: 2, "Brazil", 5, 6, 6
Row3: 1, "Brazil", 2, 2, 1
This is the Query example how i compare:
'3' as RowNumber,t1.Country ,t2.col1 -t1.col1 AS Col1Diff ,t2.col2 -t1.col2 AS Col2Diff,t2.col3 -t1.col3 AS Col3Diff
FROM (SELECT 1 as RowNumber,'Brazil' as Country,2 As col1,3 As col2,5 As col3)AS T1
INNER JOIN ( SELECT 2 as RowNumber,'Brazil' as Country,5 As col1,6 As col2,6 As col3)AS T2 on T1.Country = T2.Country
The first and sec row was group by result from different table, and i need the third row the show the different. i tried the inner join on same table method, but it does not behave like i wish.
I can only have the different row, and i lost the 1st and sec row. Of course i can select again and UNION back, but considering the performance issue. It just not a good idea.
Does anyone have idea on this ?
I'm using SQL Server 2008 R2
Jan 28, 2015 08:51 AM|Zhi Lv - MSFT|LINK
As for this issue, I suppose you need to create a temporary table and use the ABS function, and perhaps you need to use stored procedure.
From my point of view, you could query the initial table and get the first two lines and insert them into the temporary table. Then query the initial table and use the ABS function
to get the third line, and insert it to the temporary table. At last, query the temporary table, and get the desired values.
Here are some relevant articles, you could refer to them.
Temporary Tables in SQL Server: https://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/