Find the latest itemid

Hi ,

I have a requirement to look for the recent adjusted itemid for a particular id. My query doesn't give me the correct result especially the id='089780'. what is the missing code for my script or is there any other approach. I'm running 5 to 10 million records.

Declare  @sample table
(id nvarchar (35),
sourceid int,
itemid nvarchar(35),
dt_receipt datetime)

insert @sample values ('52164',7,'JOV6GLD','2017-02-10 06:27:57.000')
insert @sample values ('52164',7,'LP16GLD','2016-09-23 03:44:03.000')
insert @sample values ('52164',3,'P16GLD','2016-07-13 03:59:42.000')


insert @sample values ('52060',3,'P80GRY','2017-08-27 03:32:10.000')
insert @sample values ('52060',7,'SOP64GRY','2016-10-18 03:34:33.000')
insert @sample values ('52060',3,'P64GRY','2016-08-27 03:32:10.000')
insert @sample values ('52000',3,'SP64GRY','2017-02-10 06:28:07.000')
insert @sample values ('52001',3,'SP64GRY','2017-06-08 04:36:33.000')
insert @sample values ('52001',3,'SP64GRY','2017-01-27 05:37:10.000')
insert @sample values ('52006',3,'SP40GRY','2017-10-27 05:37:10.000')
insert @sample values ('52268',3,'LOP50GLD','2017-07-30 06:27:57.000')

insert @sample values ('52268',3,'LOV16GLD','2017-08-30 06:27:57.000')
insert @sample values ('52268',3,'LOP16GLD','2017-06-30 06:27:57.000')
insert @sample values ('52268',7,'LLP16GLD','2017-06-23 03:44:03.000')
insert @sample values ('52268',3,'LP16GLD','2017-05-13 03:59:42.000')

 
insert @sample values ('089780',3,'SPV3616SIL','2017-05-05 00:18:40.000')
insert @sample values ('089780',3,'SPV3SIL','2017-07-11 08:32:01.000')
insert @sample values ('089780',7,'LACS6SL','2017-06-04 08:15:43.000')
insert @sample values ('089780',7,'TIG36SIL','2018-01-17 01:25:37.000')
insert @sample values ('089780',7,'BBB36SIL','2018-01-23 01:25:37.000')


select r.id, r.sourceid, r.itemid, r.dt_receipt, s.itemid as new, s.dt_receipt
from @sample r
		outer apply (

					select  top 1 k.id,k.dt_receipt, k.itemid 
					from (
		
					select  T1.id ,T1.dt_receipt, T1.itemid,
							ROW_NUMBER() over(order by dt_receipt ) as rnk  
					from	@sample T1 
					where	t1.sourceid=7
							and T1.id=r.id and T1.dt_receipt > r.dt_receipt
					      
					) K 
					)	s	
where r.sourceid=3




ID-----source--orig_itemi-----dt_receipt----------new_itemid----
--------------------------------------------------------------
52164---3---P16GLD-------2016-07-13 03:59:42.000---JOV6GLD----
52060---3---P80GRY-------2017-08-27 03:32:10.000---NULL-------
52060---3---P64GRY-------2016-08-27 03:32:10.000---SOP64GRY---
52000---3---SP64GRY------2017-02-10 06:28:07.000---NULL-------
52001---3---SP64GRY------2017-06-08 04:36:33.000---NULL-------
52001---3---SP64GRY------2017-01-27 05:37:10.000---NULL-------
52006---3---SP40GRY------2017-10-27 05:37:10.000---NULL-------
52268---3---LOP50GLD-----2017-07-30 06:27:57.000---NULL-------
52268---3---LOV16GLD-----2017-08-30 06:27:57.000---NULL-------
52268---3---LOP16GLD-----2017-06-30 06:27:57.000---NULL-------
52268---3---LP16GLD------2017-05-13 03:59:42.000---LLP16GLD---
089780--3---SPV3616SIL---2017-05-05 00:18:40.000---LACS6SL----
089780--3---SPV3SIL------2017-07-11 08:32:01.000---TIG36SIL---

What I have done is match all id for source=7 with the dt_receipt of source=3 the used row_number partition with ID and dt_receipt of source=3 to get the latest per ESN and dt_receipt..

Try this

Query
select top(1) with ties
       a.id
      ,a.sourceid as source
      ,a.itemid as orig_itemid
      ,a.dt_receipt
      ,b.itemid as new_itemid
  from @sample as a
       left outer join @sample as b
                    on b.id=a.id
                   and b.sourceid=7
                   and b.dt_receipt>a.dt_receipt
 where a.sourceid=3
 order by row_number() over(partition by a.id
                                        ,a.sourceid
                                        ,a.itemid
                                        ,a.dt_receipt
                                order by b.dt_receipt desc
                           )
;

Hi Bitmed,

Thanks for the reply. Just try the script i notice the result for this ID 089780 is incorrect.

id source orig_itemid dt_receipt new_itemid
089780 3 SPV3616SIL 2017-05-05 00:18:40.000 TIG36SIL --should be LACS6SL

You need to define

I thought "the latest" would be the highest dt_receipt with the same id and sourceid=7

Do you get correct result if you omit "desc" in order by clause?

try to removed the "desc" still not getting the correct result.

--Incorrect records
id---source--orig_itemid----dt_receipt---------------new_itemid-----
089780--3----SPV3616SIL-----2017-05-05 00:18:40.000--BBB36SIL
089780--3----SPV3SIL--------2017-07-11 08:32:01.000--BBB36SIL

--Correct records
id---source--orig_itemid----dt_receipt---------------new_itemid-----
089780--3----SPV3616SIL-----2017-05-05 00:18:40.000--LACS6SL
089780--3----SPV3SIL--------2017-07-11 08:32:01.000--BBB36SIL

In your original post, you wanted:
089780--3---SPV3616SIL---2017-05-05 00:18:40.000---LACS6SL----
089780--3---SPV3SIL------2017-07-11 08:32:01.000---TIG36SIL---

In your latest post, you want:
089780--3----SPV3616SIL-----2017-05-05 00:18:40.000--LACS6SL
089780--3----SPV3SIL--------2017-07-11 08:32:01.000--BBB36SIL

Hmm :-/

This will give you the latter

Query
with cte
  as (select id
            ,sourceid
            ,itemid
            ,dt_receipt
            ,row_number() over(partition by id
                                   order by dt_receipt
                              )
             as rn
        from @sample
       where sourceid=3
     )
select top(1) with ties
       a.id
      ,a.sourceid
      ,a.itemid as orig_itemid
      ,a.dt_receipt
      ,c.itemid as new_itemid
  from cte as a
       left outer join cte as b
                    on b.id=a.id
                   and b.sourceid=a.sourceid
				   and b.rn=a.rn+1
       left outer join @sample as c
               on c.id=a.id
              and c.sourceid=7
              and c.dt_receipt>=a.dt_receipt
              and c.dt_receipt<isnull(b.dt_receipt,cast('9999-12-31' as datetime))
 order by row_number() over(partition by a.id
                                        ,a.sourceid
                                        ,a.itemid
                                        ,a.dt_receipt
                                order by c.dt_receipt desc
                           )
;

If you want the former, remove the desc in the order by clause.