none
Merging Rows

    Question

  • I have a table structure as follows


    Student(Id,First_Name, Last_Name, email, Contact, Address1,Address2,City,Edit_Date,Create_Date,Archived)

    Now if there is more than one row with same email the one with the latest edit date should be updated with missing fields by using same field value other rows (if the field is present in more than one row, the one with the next latest edit date is to be considered) and the archived status of all rows with same email except this master row must be set to 1.

    The Create_Date must be set to the minimum of all the create_date values of rows with same email value


    Please let me know how to get the desired result



    Thanks

    Saturday, June 29, 2013 12:51 AM

Answers

  • If i understand correctly, you can try something like this,

    declare  @student table(Id int,
    First_Name nvarchar(20), Last_Name nvarchar(20), email nvarchar(20), 
    Contact nvarchar(20), Address1 nvarchar(20),Address2 nvarchar(20),City nvarchar(20),
    Edit_Date datetime,Create_Date datetime,Archived int)
    -----------------------
    insert into @student values(1,'fname1','lname1','abc1@xyz.com','contact1','address1','address2','city1',getdate()-10,getdate(),null)
    insert into @student values(1,'fname1','lname1','abc1@xyz.com','contact1','address1','address2','city1',getdate()-7,getdate(),null)
    insert into @student values(1,null,null,'abc1@xyz.com','contact1','address1','address2','city1',getdate()-6,getdate(),null)
    insert into @student values(2,'fname2','lname2','abc2@xyz.com','contact2','address2','address22','city2',getdate()-5,getdate(),null)
    insert into @student values(2,'fname2','lname2','abc2@xyz.com','contact2','address2','address22','city2',getdate(),getdate(),null)
    --------------query1 latest edit date
    ;with cte1 as (
    select *
    from (
    	select *,rank() over (partition by email order by edit_date desc) as rankid from @student
    ) X
    where rankid =1
    and id in (
    			select id
    			from @student
    			group by id,email
    			having count(email)>1
    )
    ),
    --------------query2 next edit date
    cte2 as (
    select *
    from (
    	select *,rank() over (partition by email order by edit_date desc) as rankid from @student
    ) X
    where rankid =2 
    and id in (
    			select id
    			from @student
    			group by id,email
    			having count(email)>1
    )
    )
    ------------------ Master rows
    select a.id,
    case when a.first_name is null then b.first_name else a.first_name end as first_name,
    case when a.last_name is null then b.last_name else a.last_name end as last_name,a.email,
    case when a.address1 is null then b.address1 else a.address1 end as address1,
    case when a.address2 is null then b.address2 else a.address2 end as address2,
    case when a.city is null then b.city else a.city end as city,a.edit_date,a.create_date,0 as archive,a.rankid
    from cte1 a
    inner join cte2 b
    on a.id=b.id
    -------------------
    select *,rank() over (partition by email order by edit_date desc) as rankid from @student


    Regards, RSingh

    Saturday, June 29, 2013 4:32 AM