# how to get difference of two rows as third row

### Question

• hi..

i want to calculate variance of row..for example salary of employee

 emp_code month basic hra splall da lta a001 jan 10000 5000 5000 8000 7000 a001 feb 10000 4000 5000 7000 9000 a001 diff 0 -1000 0 -1000 2000

first two row are in table, i have to get third row for all emp. as above format...fields may increase/ decrease..

• Edited by Thursday, January 09, 2014 7:26 PM
Thursday, January 09, 2014 7:24 PM

• hi

i have made only 2 columns(emp_code, month) as static , please find the below query

```Create table emptab(
emp_code varchar(10),
month varchar(10),
basic  int,hra  int,  splall int, da int,lta int)
insert into  emptab
select 'a001',  'jan', 10000, 5000, 5000, 8000, 7000  union all
select 'a001',  'feb', 10000, 4000 ,5000 ,7000, 9000
Declare @columnnames varchar(1000)
select @columnnames = coalesce(@columnnames+',','')+ 'a.['+name+']-b.['+name+'] AS ['+name+']' from sys.columns
where object_id = object_id('emptab') and name not in('emp_code','month')
declare @sql varchar(max) =
'with CTE1 AS
(select row_number() over(partition by emp_code order by convert(datetime,''01-''+month+''2014'') desc) rno,* From emptab)
select * From CTE1 union all
select 3 as rno,a.emp_code,''diff'' as Month,'+ @columnnames+' from CTE1 a inner join CTE1 b on a.emp_code=b.emp_code and a.rno = b.rno-1
and a.rno = 1
order by emp_code,rno'
exec (@sql)```

Thanks

Saravana Kumar C

• Marked as answer by Friday, January 17, 2014 5:46 PM
Monday, January 13, 2014 8:46 AM

### All replies

• What is your SQL Server version, how many rows may be in the table, what dictates the sort order and do you want to insert rows for all differences (e.g. 2-1, 3-2, 4-3)?

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Thursday, January 09, 2014 7:38 PM
• Here is the SQL for your requirement.

create table emp
(
ID varchar(10),
basic int,
hra int,
mon varchar(10))

insert into emp values('a001',10000,5000,'Jan')
insert into emp values('a001',90000,4000,'feb')

select * from emp
union all
SELECT t1.ID, min(t2.basic - t1.basic),min(t2.hra - t1.hra),'diff'
FROM emp t1
left join emp t2 on t1.id = t2.id
group by t1.id

Thursday, January 09, 2014 7:50 PM

for the extent it help me, but what happen when we have variable number of columns.

as it may increase or decrease as medall, travelall.....etc

is there a way to do it by cursor...please guide

Monday, January 13, 2014 6:36 AM
• How are you generating those columns? Is the result obtained by pivotting values in a table field?

Monday, January 13, 2014 6:51 AM
• Thanks for interest ...

my sql server is 2008r2, there may be many rows mean many employee and i have to get all employee list with difference. , and sort order is by emp_code, it would be better if i insert it, if not no problem.

Monday, January 13, 2014 7:27 AM
• hi

i have made only 2 columns(emp_code, month) as static , please find the below query

```Create table emptab(
emp_code varchar(10),
month varchar(10),
basic  int,hra  int,  splall int, da int,lta int)
insert into  emptab
select 'a001',  'jan', 10000, 5000, 5000, 8000, 7000  union all
select 'a001',  'feb', 10000, 4000 ,5000 ,7000, 9000
Declare @columnnames varchar(1000)
select @columnnames = coalesce(@columnnames+',','')+ 'a.['+name+']-b.['+name+'] AS ['+name+']' from sys.columns
where object_id = object_id('emptab') and name not in('emp_code','month')
declare @sql varchar(max) =
'with CTE1 AS
(select row_number() over(partition by emp_code order by convert(datetime,''01-''+month+''2014'') desc) rno,* From emptab)
select * From CTE1 union all
select 3 as rno,a.emp_code,''diff'' as Month,'+ @columnnames+' from CTE1 a inner join CTE1 b on a.emp_code=b.emp_code and a.rno = b.rno-1
and a.rno = 1
order by emp_code,rno'
exec (@sql)```

Thanks

Saravana Kumar C

• Marked as answer by Friday, January 17, 2014 5:46 PM
Monday, January 13, 2014 8:46 AM
• But you always have just 2 rows per employee, right? In this case it would be relatively simple query.

Start from non dynamic version of it. Once you got non dynamic version working correctly, it should not be hard to convert the query to dynamic.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Monday, January 13, 2014 4:27 PM
• Start from non dynamic version of it. Once you got non dynamic version working correctly, it should not be hard to convert the query to dynamic.

you are correct,  i too posted above script, starting from a simple select statement then converted in to Dynamic SQL based on the columns given excluding('emp_code','month')

Thanks

Saravana Kumar C

Monday, January 13, 2014 6:11 PM
• thanks..

it helps me a lot.

in my scenario  i have a compete table of many employes. for months april to march (financial year).

and required a query to run every month. which takes two month input and return variance of all employee

with two month data in above format.

Friday, January 17, 2014 5:51 PM