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 Wednesday, September 02, 2015 6:20 AM