SQL query to create new column

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
September 2nd, 2015 6:19am

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

Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 7:32am

Hi Eric,  Sorry, I forgot to add that ESN in my sample data which it should be included.  your second question. what if there are more than 2 desc in #op?  The #op table is a table for operation. it consist of different description with different opid. just put only 2 desc.

I used only to look up the opid to get the opdesc because i have only opid in my main table.

  • Edited by Lenoj Wednesday, September 02, 2015 8:51 AM
September 2nd, 2015 8:50am

create 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')
	
;with mycte as (	
select 
		t.esn, t.dateaudit, t.opid , max( t.opid)Over(Partition by   t.esn ) maxOpid,
		rn = rank() over (partition by t.esn order by dateaudit  asc)
from #test t)
Select m.esn,m.dateaudit, m.opid,p.opdesc,m.rn from mycte m

left join #op p on p.opid  = m.maxOpid   
where dateaudit between ('2015-01-01') and ('2015-09-01') and rn=1


drop table #test,#op

Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 1:14pm

Thank you Li,

There's a changes and here is the final result set by running the query of my last CTE Routines. Joining the #trantable to my final result set and matches the ESN if there's an opid with a value of 51 , create a new column and place a 'sellof' while if the last opid is 5 or if locationid is no null need to put the value of location. attaching a sample desired result and the final result of my last CTE and also the #trantable (this is the transaction table).  Any  idea or help  on how to achive this result set. specially getting the ESN if it contains opid 51.

note: as you can see in the #transtable, there's a multiple esn transaction with different auditdate.

--final result set returned by the running query (inside CTE)-last CTE routines
Create table #result
(esn nvarchar(35), recoveresn nvarchar(35), ESNReceipt_Date datetime, ESNReceipt_Date2 datetime, location varchar(10))
insert into #result(esn,recoveresn,ESNReceipt_Date,ESNReceipt_Date2,location)values('352452060499834','352452060499834','2015-05-12 20:32:39.490','2015-05-12 20:32:39.490','FG')
insert into #result(esn,recoveresn,ESNReceipt_Date,ESNReceipt_Date2,location)values('352452060499834','352452060499834','2015-07-06 17:35:14.210','2015-07-06 17:35:14.210','FG')
insert into #result(esn,recoveresn,ESNReceipt_Date,ESNReceipt_Date2,location)values('990002597389764','990002597389764','2015-04-08 09:35:26.587','2015-04-08 09:35:26.587','FG')
insert into #result(esn,recoveresn,ESNReceipt_Date,ESNReceipt_Date2,location)values('990002597389764','990002597389764','2015-05-12 22:32:17.547','2015-05-12 22:32:17.547','FG')
insert into #result(esn,recoveresn,ESNReceipt_Date,ESNReceipt_Date2,location)values('990002597389764','990002597389764','2015-06-22 22:27:56.423','2015-06-22 22:27:56.423','FG')
insert into #result(esn,recoveresn,ESNReceipt_Date,ESNReceipt_Date2,location)values('T9000000000019762198',NULL,NULL,NULL,NULL)
insert into #result(esn,recoveresn,ESNReceipt_Date,ESNReceipt_Date2,location)values('T9000000000019829505',NULL,NULL,NULL,NULL)
select * from #result 


--Transaction table
Create table #TranTable
(esn nvarchar(35), 	opid	int, audit_date datetime)
insert into #TranTable(esn, opid, audit_date)values('352452060499834',4,'2015-05-12 20:32:37.383')
insert into #TranTable(esn, opid, audit_date)values('352452060499834',5,'2015-05-12 20:32:39.490')
insert into #TranTable(esn, opid, audit_date)values('352452060499834',4,'2015-07-06 17:35:12.090')
insert into #TranTable(esn, opid, audit_date)values('352452060499834',5,'2015-07-06 17:35:14.210')
insert into #TranTable(esn, opid, audit_date)values('990002597389764',4,'2015-04-08 09:35:24.547')
insert into #TranTable(esn, opid, audit_date)values('990002597389764',5,'2015-04-08 09:35:26.587')
insert into #TranTable(esn, opid, audit_date)values('990002597389764',4,'2015-05-12 22:32:15.560')
insert into #TranTable(esn, opid, audit_date)values('990002597389764',5,'2015-05-12 22:32:17.547')
insert into #TranTable(esn, opid, audit_date)values('990002597389764',4,'2015-06-22 22:27:54.320')
insert into #TranTable(esn, opid, audit_date)values('990002597389764',5,'2015-06-22 22:27:56.423')
insert into #TranTable(esn, opid, audit_date)values('T9000000000019762198',4,'2015-01-03 23:30:11.820')
insert into #TranTable(esn, opid, audit_date)values('T9000000000019762198',5,'2015-01-03 23:30:13.697')
insert into #TranTable(esn, opid, audit_date)values('T9000000000019829505',4,'2015-01-14 15:18:43.943')
insert into #TranTable(esn, opid, audit_date)values('T9000000000019829505',5,'2015-01-14 15:18:45.620')
insert into #TranTable(esn, opid, audit_date)values('T9000000000019829505',51,'2015-08-24 11:21:50.397')

select esn, recoveresn, ESNReceipt_Date, ESNReceipt_Date2, location   
from #result

My Last CTE Routines.

), cte5 as
(

	select *
	from
	( 
		select 
				 m.esn_no				    as WH_ESNReceived
				,c4.Recovered_ESN       
				,c4.ESNReceipt_Date 
				,m.ESNReceipt_Date	        as ESN_Received_Date
				,m.rma_type_id              as Recovered_RMA_Type
				,case when c4.prodstatus=4 then '' else c4.wmslocationid end as WMSlocationid
		from Cte4 c4 
		right join #BatchTemp m
		ON  c4.ESN_Received  = m.esn_no and c4.ESNReceipt_Date = m.ESNReceipt_Date 
	) shp
)
select  t.WH_ESNReceived, t.Recovered_ESN, t.ESNReceipt_Date, ESNReceipt_Date, WMSlocationid 
from cte5 t order by WH_ESNReceived 
 

Sample Disired result:

WH_ESNReceived--------Recovered_ESN-----ESNReceipt_Date----------ESNReceipt_Date----------WMSlocationid--New Column --new Column date
---------------------------------------------------------------------------------------------------------------------------------
352452060499834-------352452060499834---2015-05-12 20:32:39.490--2015-05-12 20:32:39.490--FG-L----------FG-----------05-12 20:32:39.490
352452060499834-------352452060499834---2015-07-06 17:35:14.210--2015-07-06 17:35:14.210--FG-L----------FG-----------2015-07-06 17:35:14.210
990002597389764-------990002597389764---2015-04-08 09:35:26.587--2015-04-08 09:35:26.587--FG-L----------FG-----------2015-04-08 09:35:26.587
990002597389764-------990002597389764---2015-05-12 22:32:17.547--2015-05-12 22:32:17.547--FG-L----------FG-----------2015-05-12 22:32:17.547
990002597389764-------990002597389764---2015-06-22 22:27:56.423--2015-06-22 22:27:56.423--FG-L----------FG-----------2015-06-22 22:27:56.423
T9000000000019762198--NULL--------------NULL---------------------NULL---------------------NULL----------NULL---------NULL
T9000000000019829505--NULL--------------NULL---------------------NULL---------------------NULL----------Selloff------2015-08-24 11:21:50.397


  • Edited by Lenoj 21 hours 5 minutes ago
September 3rd, 2015 5:57am

Hi, Just made the query and get the desired result set. I also added new column in #transact and #result.  not yet testing on the actual data. hoping this solved my problem. thank you.

--final result set returned by the running query (inside CTE)-last CTE routines
Create table #result
(esn nvarchar(35), recoveresn nvarchar(35), ESNReceipt_Date datetime, ESNReceipt_Date2 datetime, location varchar(10), batchesn_id nvarchar(35))
insert into #result(esn,recoveresn,ESNReceipt_Date,ESNReceipt_Date2,location,batchesn_id)values('352452060499834','352452060499834','2015-05-12 20:32:39.490','2015-05-12 20:32:39.490','FG','S20150512-00295')
insert into #result(esn,recoveresn,ESNReceipt_Date,ESNReceipt_Date2,location,batchesn_id)values('352452060499834','352452060499834','2015-07-06 17:35:14.210','2015-07-06 17:35:14.210','FG','S20150706-00166')
insert into #result(esn,recoveresn,ESNReceipt_Date,ESNReceipt_Date2,location,batchesn_id)values('990002597389764','990002597389764','2015-04-08 09:35:26.587','2015-04-08 09:35:26.587','FG','S20150408-00167')
insert into #result(esn,recoveresn,ESNReceipt_Date,ESNReceipt_Date2,location,batchesn_id)values('990002597389764','990002597389764','2015-05-12 22:32:17.547','2015-05-12 22:32:17.547','FG','S20150512-00312')
insert into #result(esn,recoveresn,ESNReceipt_Date,ESNReceipt_Date2,location,batchesn_id)values('990002597389764','990002597389764','2015-06-22 22:27:56.423','2015-06-22 22:27:56.423','FG','S20150622-00175')
insert into #result(esn,recoveresn,ESNReceipt_Date,ESNReceipt_Date2,location,batchesn_id)values('T9000000000019762198',NULL,NULL,NULL,NULL,'S20150103-00118')
insert into #result(esn,recoveresn,ESNReceipt_Date,ESNReceipt_Date2,location,batchesn_id)values('T9000000000019829505',NULL,NULL,NULL,NULL,'S20150114-00278')
select * from #result 


--Transaction table
Create table #TranTable
(esn nvarchar(35), 	opid	int, audit_date datetime, batchesn_id nvarchar(35))
insert into #TranTable(esn, opid, audit_date,batchesn_id)values('352452060499834',4,'2015-05-12 20:32:37.383','S20150512-00295')
insert into #TranTable(esn, opid, audit_date,batchesn_id)values('352452060499834',5,'2015-05-12 20:32:39.490','S20150512-00295')
insert into #TranTable(esn, opid, audit_date,batchesn_id)values('352452060499834',4,'2015-07-06 17:35:12.090','S20150706-00166')
insert into #TranTable(esn, opid, audit_date,batchesn_id)values('352452060499834',5,'2015-07-06 17:35:14.210','S20150706-00166')
insert into #TranTable(esn, opid, audit_date,batchesn_id)values('990002597389764',4,'2015-04-08 09:35:24.547','S20150408-00167')
insert into #TranTable(esn, opid, audit_date,batchesn_id)values('990002597389764',5,'2015-04-08 09:35:26.587','S20150408-00167')
insert into #TranTable(esn, opid, audit_date,batchesn_id)values('990002597389764',4,'2015-05-12 22:32:15.560','S20150512-00312')
insert into #TranTable(esn, opid, audit_date,batchesn_id)values('990002597389764',5,'2015-05-12 22:32:17.547','S20150512-00312')
insert into #TranTable(esn, opid, audit_date,batchesn_id)values('990002597389764',4,'2015-06-22 22:27:54.320','S20150622-00175')
insert into #TranTable(esn, opid, audit_date,batchesn_id)values('990002597389764',5,'2015-06-22 22:27:56.423','S20150622-00175')
insert into #TranTable(esn, opid, audit_date,batchesn_id)values('T9000000000019762198',4,'2015-01-03 23:30:11.820','S20150103-00118')
insert into #TranTable(esn, opid, audit_date,batchesn_id)values('T9000000000019762198',5,'2015-01-03 23:30:13.697','S20150103-00118')
insert into #TranTable(esn, opid, audit_date,batchesn_id)values('T9000000000019829505',4,'2015-01-14 15:18:43.943','S20150114-00278')
insert into #TranTable(esn, opid, audit_date,batchesn_id)values('T9000000000019829505',5,'2015-01-14 15:18:45.620','S20150114-00278')
insert into #TranTable(esn, opid, audit_date,batchesn_id)values('T9000000000019829505',51,'2015-08-24 11:21:50.397','S20150114-00278')



select a.esn, a.recoveresn, a.ESNReceipt_Date, a.ESNReceipt_Date2, a.location, a.batchesn_id ,
       c.esn , a.recoveresn , c.audit_date as ESNReceiptDate, a.location , c.opid,
       newlocation = case when location IS NOT NULL then location 
                          when location is null and opid =5 then 'NULL' 
                          when location is null and opid=51 then 'Selloff' end
from #result a-- displayed the result set returned from my last CTE routine
outer apply (
			Select  top 1 * 
			from #TranTable b where b.opid in (5,51) and a.esn=b.esn and a.batchesn_id=b.batchesn_id  order by b.audit_date desc) as c
			  

Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2015 9:37pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics