Hi,
I have a records with multiple esn with different op_type.
I need to retain only those op_type is 51 while those with multiple esn but with out Op_type is equal to 51 will retain or include in the records.
Please see below sample DDL and the expected Result. thank you
create table #test
(esn nvarchar(35), audit_date datetime, op_type int, op_desc varchar(35))
insert into #test(esn,audit_date,op_type, op_desc) values('990002597389764','2015-04-08 09:35:26.587',5,'Shipping')
insert into #test(esn,audit_date,op_type, op_desc) values('990002597389764','2015-05-12 22:32:17.547',5,'Shipping')
insert into #test(esn,audit_date,op_type, op_desc) values('990002597389764','2015-06-22 22:27:56.423',5,'Shipping')
insert into #test(esn,audit_date,op_type, op_desc) values('T9000000000019761824','2015-01-03 19:23:54.980',5,'Shipping')
insert into #test(esn,audit_date,op_type, op_desc) values('T9000000000019794182','2015-01-09 15:36:07.060',5,'Shipping')
insert into #test(esn,audit_date,op_type, op_desc) values('T9000000000020004932','2015-02-10 15:18:47.020',5,'Shipping')
insert into #test(esn,audit_date,op_type, op_desc) values('T9000000000020004932','2015-08-24 10:45:21.040',51,'Scrap')
Select
esn, audit_date, op_type ,op_desc
From #test
Where
audit_date between ('2015-01-01') and ('2015-08-28')
and op_type in (5,51)
received_esn---------date--------------------type_id---type_description
==============================================================================
990002597389764------2015-04-08 09:35:26.587----5-----Shipping
990002597389764------2015-05-12 22:32:17.547----5-----Shipping
990002597389764------2015-06-22 22:27:56.423----5-----Shipping
T9000000000019761824-2015-01-03 19:23:54.980----5-----Shipping
T9000000000019794182-2015-01-09 15:36:07.060----5-----Shipping
T9000000000020004932-2015-08-24 10:45:21.040----51----Scrap