Hi,

I have a data with mutliple esn but different auditdate and opid. I will pull this data filtering by date and opid. my requirements is not to include the opid = 51 but need to get the desired opdesc for this esn that contains opid=51. please see below sample ddl and desired result. thank you.

I dont want do include the opid = 51 because it will create a duplicate in transaction instead retain the opid 5

example: esn T9000000000019829505 has multiple rows with different auditdate and opid. retain the records for opid is equal to 5 but get the opdesc for opid is equal 51.

reate table #test
(esn nvarchar(35), dateaudit datetime, opid int)

insert into #test(esn,dateaudit, opid)values('352452060499834','2015-05-12 20:32:39.490',5)	
insert into #test(esn,dateaudit, opid)values('352452060499834','2015-07-06 17:35:14.210',5)	
insert into #test(esn,dateaudit, opid)values('T9000000000019829505','2015-01-14 15:18:45.620',5)	
insert into #test(esn,dateaudit, opid)values('T9000000000019829505','2015-08-24 11:21:50.397',51)
insert into #test(esn,dateaudit, opid)values('990002597389764','2015-06-22 22:27:56.423',5)
insert into #test(esn,dateaudit, opid)values('990002597389764','2015-04-08 09:35:26.587',5)
insert into #test(esn,dateaudit, opid)values('990002597389764','2015-05-12 22:32:17.547',5)


create table #op
(opid int, opdesc varchar(35))
insert into #op(opid,opdesc)values(5,'Shipping')
insert into #op(opid,opdesc)values(51,'Scrap')
	
	
select 
		t.esn, t.dateaudit, t.opid, p.opdesc,
		rn = rank() over (partition by t.esn order by t.opid asc)
from #test t
left join #op p on t.opid = p.opid 
where dateaudit between ('2015-01-01') and ('2015-09-01')



Desired Result:

esn-------------------dateaudit----------------opid	--opdesc--rn
352452060499834	------2015-05-12 20:32:39.490---5---Shipping--1
352452060499834	------2015-07-06 17:35:14.210--	5---Shipping--1
T9000000000019829505--2015-01-14 15:18:45.620--	5---Scrap-----1

OR

esn-------------------dateaudit----------------opid	--opdesc--rn--remarks
352452060499834	------2015-05-12 20:32:39.490---5---Shipping--1---shipping
352452060499834	------2015-07-06 17:35:14.210--	5---Shipping--1---shipping
T9000000000019829505--2015-01-14 15:18:45.620--	5---Shipping--1---Scrap

• Edited by Lenoj 1 hour 27 minutes ago

Hi Lenoj,

Your requirement is not clear, can you clarify below questions?

• Why the esn 990002597389764 got excluded?
• What if there're more than 2 desc in table #op

Please answer the above questions so that we can give further advice. If you have any question, feel free to let me