none
How to find row changes of a column in sql server RRS feed

  • Question

  • Hi,

    Can you please help on query i have a table like below

    create table employee(empid int,name varchar(100),location varchar(100),deptname varchar(100))

    insert into employee values(1,'abc','us','it')
    insert into employee values(1,'abc','it','it')
    insert into employee values(1,'abc','fr','it')
    insert into employee values(2,'xyz','uk','sales')
    insert into employee values(3,'xcd','it','it')
    insert into employee values(3,'xcd','fr','it')

    select *From employee

    i need  query to find all rows where location has been changed and my output will be like below

    empid name location deptname

    1 abc us it
    1 abc it it
    1 abc fr it
    3 xcd it it
    3 xcd fr it


    • Edited by prabhu00 Thursday, December 5, 2019 5:46 AM
    Thursday, December 5, 2019 5:44 AM

All replies

  • Please check if below query works:

    SELECT DISTINCT a.empid, a.name, a.location, a.deptname
    FROM employee a
    JOIN employee AS b ON a.empid = b.empid
    	AND a.location <> b.location


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Thursday, December 5, 2019 6:01 AM
  • create table employee(empid int,name varchar(100),location varchar(100),deptname varchar(100))
    insert into employee values(1,'abc','us','it')
    ,(1,'abc','it','it')
    ,(1,'abc','fr','it')
    ,(2,'xyz','uk','sales')
    ,(3,'xcd','it','it')
    ,(3,'xcd','fr','it');
    
    --option 1
    Select empid,name,location,deptname 
    from employee 
    where empid in
    (select empid
    From employee
    group by empid
    having( count(distinct location)>1)
    )
    
    
    --option 2
    ;with mycte as (
    select empid
    From employee
    group by empid
    having( count(distinct location)>1)
    
    )
    
    select e.empid,e.name,e.location,e.deptname
    from employee e 
    join mycte m on m.empid=e.empid
    
    --option 3
    ;with mycte as (
    SELECT  empid,name,location,deptname ,
    dense_rank() over( Partition by empid Order by  location) 
    + dense_rank() over(Partition by empid Order by  location desc) - 1 as dr 
    FROM employee  
     )
      
     
     select empid,name,location,deptname 
     from mycte
     where dr>1
    
    drop table employee

    Thursday, December 5, 2019 2:48 PM
    Moderator
  • Hi prabhu00,

    Please try following script.

    --drop table employee
    go
    create table employee
    (empid int,name varchar(10),location varchar(10),deptname varchar(10))
    insert into employee values(1,'abc','us','it')
    ,(1,'abc','it','it')
    ,(1,'abc','fr','it')
    ,(2,'xyz','uk','sales')
    ,(3,'xcd','it','it')
    ,(3,'xcd','fr','it');
    
    ;with cte as (
    select *,
    case when lead(location)over(partition by empid,name order by location)<> location and lead(location)over(partition by empid,name order by location) is not null 
    then 1 else 0 end value
    from employee
    ),cte1 as (
    select*,sum (value)over(partition by empid,name ) sum_value from cte )
    select empid,name,location,deptname 
    from cte1 
    where sum_value <>0
    /*
    empid       name       location   deptname
    ----------- ---------- ---------- ----------
    1           abc        fr         it
    1           abc        it         it
    1           abc        us         it
    3           xcd        fr         it
    3           xcd        it         it
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 6, 2019 8:06 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 13, 2019 9:00 AM
  • Hi,

     

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and also encourage the community member to keep working on your issues.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 20, 2019 7:18 AM