Remove one records if has a multiple records with different op_type

Transact-SQL
1

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
2

;with cte as (
Select
esn, audit_date, op_type ,op_desc, rn = rank() over (partition by esn order by op_type desc)
From #test
Where
audit_date between ('2015-01-01') and ('2015-08-28')
and op_type in (5,51)
)
select * from cte where rn = 1

3

Thank you very much.

4

Hi waterduck, I have another query. I filter the records from Jan 1 to Aug 28. Right now the report i run it by monthly. So based on the sample data i have given what if i run it monthly like for the month of February. I dont want to pull the data or include those esn that has op_desc where scrap.

So, this esn has 2 records one from Feb and the other is Aug. I dont want to include or added this ESN 'T9000000000020004932' in February but if i will run for Aug i need to add this records or added.

5

This is not a table; it has no key. Rows are not records. Among the many fundamarnal differences is that constraints on table prevetn duplicate rows and invalid data.

Your esn is really 35 Chinese Unicode characrters? It will be if you let. Encodings are generally shorter and have a fixed length.

I have a records [sic] with multiple esn with different op_type.<<

Should we have PRIMARY KEY (esn, op_type) in the DDL? No, it looks like PRIMARY KEY (esn, audit_timestamp) is the key! Did I guess right? But this is not a date; it is a timestamp (ANSI/ISO term versus MS dialect).

Since the “op_type” is numeric, you must be doing math on it. But that makes no sense. Also, why did you denormalize the schmea? We need auxillary table for the operation types.

Since audit data is never kept in the schema under audit, this time stamp scares me

CREATE TABLE Test
(esn VARCHAR(35) NOT NULL,
audit_timestamp DATETIME2(0) NOT NULL,
PRIMARY KEY (esn, audit_timestamp),
op_type CHAR(2) NOT NULL
REFERENCES Operations(op_type)
ON UPDATE CASCADE);

CREATE TABLE Operations
(op_type CHAR(2) NOT NULL CHECK (op_type LIKE '[0-9][0-9]'),
op_desc VARCHAR(35) NOT NULL
CHECK (op_desc IN ('Shipping', 'Scrap', ..));

No need to use the old Sybase insertion syntax; we have had ANSI/ISO options for many years.

INSERT INTO Test
VALUES
('990002597389764', '2015-04-08 09:35:26.587', '05'),
('990002597389764', '2015-05-12 22:32:17.547', '05'),
('990002597389764', '2015-06-22 22:27:56.423', '05'),
('T9000000000019761824', '2015-01-03 19:23:54.980', '05'),
('T9000000000019794182', '2015-01-09 15:36:07.060', '05'),
('T9000000000020004932', '2015-02-10 15:18:47.020', '05'),
('T9000000000020004932', '2015-08-24 10:45:21.040', '51');

Here is a re-write of your query in the current T-SQL dialect.

SELECT T.esn, T.audit_timestamp, T.op_type, O.op_desc
FROM Test AS T, Operations AS O
WHERE T.op_type = O.op_type
AND CAST (audit_timestamp AS DATE)
BETWEEN ('2015-01-01') AND ('2015-08-28')
AND op_type IN ('05', '51');

To find groupings with mixed op_types:

SELECT T.esn, MAX(T.op_type)
FROM Tests
GROUP BY T.esn
HAVING MIN(op_type) < MAX(op_type)

6

Thank you jcelko. There is a changes on the requirements. I change the script given by waterduct instead of using op_type in partition i change it to audit date in ascending order. basically i still get the op_type=1 instead of 51 but its possible to add new column let say remarks. the Remarks will contains the scrap. this will perform if the esn has operation equal to 51 if no then put an empty or blank. please see below sample data. Thank you.

;with cte as (
Select 
esn, audit_date, op_type ,op_desc, rn = rank() over (partition by esn order by audit_date asc)
From #test 
Where 
audit_date between ('2015-01-01') and ('2015-08-28')
 and op_type in (5,51) 
)
select esn, audit_date, op_type, op_desc, rn from cte where rn = 1

esn---------------------audit_date-------------op_type--op_desc--remarks--rn
=============================================================================
990002597389764---------2015-04-08 09:35:26.587--5-----Shipping--	--------1
T9000000000019761824----2015-01-03 19:23:54.980--5-----Shipping--	--------1
T9000000000019794182----2015-01-09 15:36:07.060--5-----Shipping--	--------1
T9000000000020004932----2015-02-10 15:18:47.020--5-----Shipping--Scrap------1