Old user and new user

Transact-SQL
1

can anyone help me with the query!!! i need the the output from #output table from the resultset of #temp table.

CREATE TABLE #Temp(OldUserID INT,NewUserID INT,ConvertedDate DATETIME)

INSERT INTO #temp(OldUserID,NewUserID,ConvertedDate)VALUES(1,2,'2012-01-01')
INSERT INTO #temp(OldUserID,NewUserID,ConvertedDate)VALUES(2,3,'2012-02-01')
INSERT INTO #temp(OldUserID,NewUserID,ConvertedDate)VALUES(3,4,'2012-03-01')
INSERT INTO #temp(OldUserID,NewUserID,ConvertedDate)VALUES(11,22,'2013-01-01')
INSERT INTO #temp(OldUserID,NewUserID,ConvertedDate)VALUES(111,22,'2013-02-01')
INSERT INTO #temp(OldUserID,NewUserID,ConvertedDate)VALUES(22,33,'2012-02-01')

SELECT * FROM #Temp

CREATE TABLE #Output(OldUserID INT,NewUserID INT,ConvertedDate DATETIME,LatestUserID INT)

INSERT INTO #Output(OldUserID,NewUserID,ConvertedDate,LatestUserID)VALUES(1,2,'2012-01-01',4)
INSERT INTO #Output(OldUserID,NewUserID,ConvertedDate,LatestUserID)VALUES(2,3,'2012-02-01',4)
INSERT INTO #Output(OldUserID,NewUserID,ConvertedDate,LatestUserID)VALUES(3,4,'2012-03-01',4)
INSERT INTO #Output(OldUserID,NewUserID,ConvertedDate,LatestUserID)VALUES(11,22,'2013-01-01',33)
INSERT INTO #Output(OldUserID,NewUserID,ConvertedDate,LatestUserID)VALUES(111,22,'2013-02-01',33)
INSERT INTO #Output(OldUserID,NewUserID,ConvertedDate,LatestUserID)VALUES(22,33,'2012-02-01',33)

SELECT * FROM #Output

2

can you post your desired output?

3

the desired output is the output of #Output table.

below is the needed output

OldUser New User LatestUser
1 2 4
2 3 4
3 4 4
11 22 33
111 22 33
22 33 33

4

Try this:

with cte(olduserid,newuserid,newestuserid)
  as (select a.olduserid
            ,a.newuserid
            ,a.newuserid
        from #temp as a
             left outer join #temp as b
                          on b.olduserid=a.newuserid
       where b.olduserid is null
      union all
      select a.olduserid
            ,a.newuserid
            ,b.newestuserid
        from #temp as a
             inner join cte as b
                     on b.olduserid=a.newuserid
     )
select *
  from cte
 order by olduserid
;
5

this is exactly what i needed. Thanks!!!

6

what if there are circular re-numbering? for example...

from To Latest
1 2 3 '2015-01-01'
2 3 3 '2015-02-01'
3 4 3 '2015-03-01
4 3 3 '2015-04-01'

is this even a possible output?

7

Could you be looking for this?

with cte1(olduserid,newuserid,converteddate,rn1)
  as (select a.olduserid
            ,a.newuserid
            ,a.converteddate
            ,row_number() over(order by (select null)) as rn1
        from #temp as a
             left outer join #temp as b
                          on b.newuserid=a.olduserid
       where b.olduserid is null
      union all
      select a.olduserid
            ,a.newuserid
            ,a.converteddate
            ,b.rn1
        from #temp as a
             inner join cte1 as b
                     on b.newuserid=a.olduserid
                    and b.converteddate<=a.converteddate
     )
    ,cte2(olduserid,newuserid,rn1,rn2)
  as (select olduserid
            ,newuserid
            ,rn1
            ,row_number() over(partition by rn1 order by converteddate desc) as rn2
        from cte1
     )
    ,cte3(olduserid,newuserid,newestuserid)
  as (select b.olduserid
            ,b.newuserid
            ,a.newuserid as newestuserid
        from cte2 as a
             inner join cte1 as b
                     on b.rn1=a.rn1
       where a.rn2=1
     )
select *
  from cte3
 order by olduserid
;
8

actually NO

below is the input and output

CREATE TABLE #Temp(OldUserID INT,NewUserID INT,ConvertedDate DATETIME)

INSERT INTO #temp(OldUserID,NewUserID,ConvertedDate)VALUES(1,2,'2012-01-01')
INSERT INTO #temp(OldUserID,NewUserID,ConvertedDate)VALUES(2,3,'2012-02-01')
INSERT INTO #temp(OldUserID,NewUserID,ConvertedDate)VALUES(3,4,'2012-03-01')
INSERT INTO #temp(OldUserID,NewUserID,ConvertedDate)VALUES(11,22,'2013-01-01')
INSERT INTO #temp(OldUserID,NewUserID,ConvertedDate)VALUES(111,22,'2013-02-01')
INSERT INTO #temp(OldUserID,NewUserID,ConvertedDate)VALUES(22,33,'2012-02-01')
INSERT INTO #temp(OldUserID,NewUserID,ConvertedDate)VALUES(33,22,'2015-02-01')

SELECT * FROM #Temp

CREATE TABLE #Output(OldUserID INT,NewUserID INT,ConvertedDate DATETIME,LatestUserID INT)

INSERT INTO #Output(OldUserID,NewUserID,ConvertedDate,LatestUserID)VALUES(1,2,'2012-01-01',4)
INSERT INTO #Output(OldUserID,NewUserID,ConvertedDate,LatestUserID)VALUES(2,3,'2012-02-01',4)
INSERT INTO #Output(OldUserID,NewUserID,ConvertedDate,LatestUserID)VALUES(3,4,'2012-03-01',4)
INSERT INTO #Output(OldUserID,NewUserID,ConvertedDate,LatestUserID)VALUES(11,22,'2013-01-01',22)
INSERT INTO #Output(OldUserID,NewUserID,ConvertedDate,LatestUserID)VALUES(111,22,'2013-02-01',22)
INSERT INTO #Output(OldUserID,NewUserID,ConvertedDate,LatestUserID)VALUES(22,33,'2012-02-01',22)
INSERT INTO #Output(OldUserID,NewUserID,ConvertedDate,LatestUserID)VALUES(33,22,'2015-02-01',22)

SELECT * FROM #Output

9

The last query I gave, was designed to the rule, that converteddate is sequential, and thus the convertiondate for userid 22 to 23 MUST be after convertiondate for userid 11 to 22 and 111 to 22. If you change convertiondate to ex. '2014-02-01' for userid 22 to 24, the query would work (except that you would get a few duplicates, which can be avoided, changing "select *" to "select distinct *".

Now if you insist on getting the converteddate aswell, you can have cte3 return the date like this:

with cte1(olduserid,newuserid,converteddate,rn1)
  as (select a.olduserid
            ,a.newuserid
            ,a.converteddate
            ,row_number() over(order by (select null)) as rn1
        from #temp as a
             left outer join #temp as b
                          on b.newuserid=a.olduserid
       where b.olduserid is null
      union all
      select a.olduserid
            ,a.newuserid
            ,a.converteddate
            ,b.rn1
        from #temp as a
             inner join cte1 as b
                     on b.newuserid=a.olduserid
                    and b.converteddate<=a.converteddate
     )
    ,cte2(olduserid,newuserid,rn1,rn2)
  as (select olduserid
            ,newuserid
            ,rn1
            ,row_number() over(partition by rn1 order by converteddate desc) as rn2
        from cte1
     )
    ,cte3(olduserid,newuserid,converteddate,newestuserid)
  as (select b.olduserid
            ,b.newuserid
            ,b.converteddate
            ,a.newuserid as newestuserid
        from cte2 as a
             inner join cte1 as b
                     on b.rn1=a.rn1
       where a.rn2=1
     )
select distinct *
  from cte3
 order by olduserid
;
10

perfect!!! i altered the date to be sequential and the query results are exactly what i am looking for. Thankss