I have a temp table with two columns of integer data i want to find the difference between two columns in 3rd column.
#TEMP1
Present previous
59 88
75 75
45 45
77 88
09 08
#temp2
Difference
29
0
0
11
-1
Is this possible ??
This question is related to
sql-server
tsql
sql-server-2008-r2
addition
temp-tables
IF the table is alias t
SELECT t.Present , t.previous, t.previous- t.Present AS Difference
FROM temp1 as t
select previous, Present, previous-Present as Difference from tablename
or
select previous, Present, previous-Present as Difference from #TEMP1
Yes, you can select the data, calculate the difference, and insert all values in the other table:
insert into #temp2 (Difference)
select previous - Present
from #TEMP1
There are many ways of doing this (and I encourage you to look them up as they will be more efficient generally) but the simplest way of doing this is to use a non-set operation to define the value of the third column:
SELECT
t1.previous
,t1.present
,(t1.present - t1.previous) as difference
FROM #TEMP1 t1
Note, this style of selection is considered bad practice because it requires the query plan to reselect the value of the first two columns to logically determine the third (a violation of set theory that SQL is based on). Though it is more complicated, if you plan on using this to evaluate more than the values you listed in your example, I would investigate using an APPLY clause. http://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx
Source: Stackoverflow.com