none
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..

    please help...


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

Answers

  • 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 VickyKumar 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
  • Thanks For reply...

    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?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    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 VickyKumar 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