Find the most recent adjusted itemid

1

Hi,
The requirements is to get the adjusted itemid for a particular ID.

I have a received items but later on they found out that the itemid is incorrect and they made an adjustment in the records. they make a transaction to change the itemid which they call adjustment entry. which I need to get the recent adjusted itemid for a particular id. the tricky part is I have a multiple receive id from a different receipt date (dt_receipt) which I have hard time on how to do this in script. the only way to differentiate is the actual receipt date per ID. below is the DDL and desired result.

Note:
When they received the id in whse but after sorting they found out the itemid is incorrect then they perform the adjustment entry.
sourceid = 3 --tag as received
sourceid = 7 --tag as adjustment

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

insert @sample values ('52164',7,'SOP16GLD','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',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 ('52268',3,'LOP16GLD','2017-06-30 06:27:57.000')
insert @sample values ('52268',7,'LOP16GLD','2017-06-23 03:44:03.000')
insert @sample values ('52268',3,'LP16GLD','2017-05-13 03:59:42.000')


;with cte as
(
	select
		id, sourceid, itemid, dt_receipt
	from @sample
), cte_receive as
(
	select
		id, sourceid, itemid, dt_receipt
	from @sample
	where sourceid=3

), cte_adjustment as
(
	select
		id, sourceid, itemid, dt_receipt
	from @sample
	where sourceid=7
)
select 
		d.id, d.sourceid, d.itemid as orig_itemid, s.itemid as new_itemid, d.dt_receipt 
from	cte_receive d  ---receive records
		left join cte_adjustment s   -- all adjustment records
		on s.id = d.id and d.dt_receipt >=s.dt_receipt 
where   d.sourceid=3 -- all receive records


		
DESIRED RESULT

id----sourceid---orig_itemid--new_itemid--	dt_receipt
===================================================================
52164---3--------P16GLD-------SOP16GLD------2016-07-13 03:59:42.000
52060---3--------P64GRY-------SOP64GRY------2016-08-27 03:32:10.000
52000---3--------SP64GRY------SP64GRY-------2017-02-10 06:28:07.000
52001---3--------SP64GRY------SP64GRY-------2017-06-08 04:36:33.000
52001---3--------SP64GRY------SP64GRY-------2017-01-27 05:37:10.000
52268---3--------LOP16GLD-----LOP16GLD------2017-06-30 06:27:57.000
52268---3--------LP16GLD------LOP16GLD-------2017-05-13 03:59:42.000
2

This appears to match the desired results...

SELECT 
	s.id,
	s.sourceid,
	orig_itemid = s.itemid,
	new_itemid = new.itemid,
	s.dt_receipt
FROM 
	#Sample s
	CROSS APPLY (
				SELECT TOP 1
					s2.itemid
				FROM 
					#Sample s2
				WHERE 
					s.id = s2.id
				ORDER BY
					s2.dt_receipt DESC
				) new
WHERE 
	s.sourceid = 3;

HTH,
Jason

1 Like
3

Thank you very much Jason.

1 Like
4

You're welcome. Glad to help. :slight_smile:

5

Hi Jason,

Can you please enlighten me the code specially with the cross apply portion in the script. thanks.

6

The outer query simply says, “Give me all rows from #Sample where the sourceid = 3”.
The “CROSS APPLY”, can be translated as “for each row of the outer query”… In this case it says, “for each row in the outer query, give me the top 1 , row where the inner ID = the outer ID, based on dt_receipt in DESC order.

1 Like
7

Thanks Jason

8

Hi Jason,

its okay to use this code to get the recent register parts for a specific id.
Please find below sample data and ddl. Thanks.

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


insert @sample values ('52164',3,'P16GLD','2017-06-13 03:59:42.000')
insert @sample values ('52164',3,'P16GLD','2016-08-1 01:59:42.000')
insert @sample values ('52060',3,'P64GRY','2016-08-27 03:32:10.000')
insert @sample values ('52000',3,'SP64GRY','2017-04-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 ('52268',3,'LOP16GLD','2017-07-25 06:27:57.000')




Declare  @register table
(id nvarchar (35),
sourceid int,
itemid nvarchar(35),
dt_register datetime)

insert @register values ('52164',1,'SOP16GLD','2017-07-10 06:27:57.000')
insert @register values ('52164',1,'LP16GLD','2016-09-23 03:44:03.000')
insert @register values ('52164',1,'P16GLD','2016-07-14 04:30:40.000')
insert @register values ('52060',1,'P64GRY','2016-09-28 09:32:10.000')
insert @register values ('52000',1,'SP64GRY','2017-02-10 06:28:07.000')
insert @register values ('52001',1,'SP64GRY','2017-07-08 10:36:33.000')
insert @register values ('52001',1,'SP64GRY','2017-03-27 11:37:10.000')


Desires Result:

id------sourceid--itemid---dt_receipt----------------dt_registered
====================================================================
52000---3-------SP64GRY----2017-04-10 06:28:07.000---2017-02-10 06:28:07.000
52001---3-------SP64GRY----2017-01-27 05:37:10.000---2017-03-27 11:37:10.000
52001---3-------SP64GRY----2017-06-08 04:36:33.000---2017-07-08 10:36:33.000
52060---3-------P64GRY-----2016-08-27 03:32:10.000---2016-09-28 09:32:10.000
52164---3-------P16GLD-----2016-08-01 01:59:42.000---2016-09-23 03:44:03.000
52164---3-------P16GLD-----2017-06-13 03:59:42.000---2017-07-10 06:27:57.000
52268---3-------LOP16GLD---2017-07-25 06:27:57.000---