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