Match values from another table and perform updates

Villanuev · September 18, 2018, 9:00am

Hi,

I have a requirements to get the tnumber from @sampleReceipt and copy to @sampleTrans using idno. To get this I have to match the following field idno, origidno and recoveridno (@sampleTrans) to idno ( @sampleReceipt ) not all are matched. Using my Query below I have a null values in tnumber which mean the condition from my left join are not satisfied. Any help is very much appreciated. Thank you.

sample data:
declare @sampleTrans table
(
idno varchar(50), origidno varchar(50), recoveridno  varchar(50), dt datetime, lineid varchar(10), ctype varchar(10), op_desc varchar(20), tnumber varchar(50),ponum varchar(10))

insert @sampleTrans values ('ABCDE5B12AG12338','ABCDE5B12AG12338','NULL','2018-09-04 19:26:49.000','H1','R2','Create',NULL,'PO0138')
insert @sampleTrans values ('ABCDE5B12AG12338','ABCDE5B12AG12338','NULL','2018-09-06 18:41:45.000','H1','R2','Finished',NULL,'PO0139')
insert @sampleTrans values ('ABCDE5B12AG12338','ABCDE5B12AG1233812345','ABCDE5B12AG12338','2018-08-30 08:29:11.000','A1','R1','Create',NULL,'PO3651')
insert @sampleTrans values ('ABCDE5B12AG12338','ABCDE5B12AG1233812345','ABCDE5B12AG12338','2018-08-31 08:35:56.000','A1','R1','Finished',NULL,'PO3659')
insert @sampleTrans values ('ABCDE5B12AG12338','ABCDE5B12AG1233812345','ABCDE5B12AG12338','2018-09-10 08:19:21.000','A2','R1','Create',NULL,'PO3661')
insert @sampleTrans values ('ABCDE5B12AG12338','ABCDE5B12AG1233812345','ABCDE5B12AG12338','2018-09-12 20:52:55.000','A2','R1','Finished',NULL,'PO3672')

insert @sampleTrans values ('ABCDE5B12AG14044','ABCDE5B12AG1404476','ABCDE5B12AG','2018-04-12 03:32:42.000','H1','R1','Create',NULL,'PO0180')
insert @sampleTrans values ('ABCDE5B12AG14044','ABCDE5B12AG1404476','ABCDE5B12AG','2018-04-18 03:11:07.000','H1','R1','Finished',NULL,'PO0181')
insert @sampleTrans values ('ABCDE5B12AG14044','ABCDE5B12AG14044','NULL','2018-09-05 20:55:44.000','H2','R2','Create',NULL,'PO0185')
insert @sampleTrans values ('ABCDE5B12AG14044','ABCDE5B12AG14044','NULL','2018-09-12 09:53:13.000','H2','R2','Finished',NULL,'PO0186')
insert @sampleTrans values ('ABCDE5B12AG14044','TABCDEFGHIGKLMNOP55','ABCDE5B12AG','2018-09-03 14:54:42.000','R1','R1','Create',NULL,'PO0183')
insert @sampleTrans values ('ABCDE5B12AG14044','TABCDEFGHIGKLMNOP55','ABCDE5B12AG','2018-09-03 23:33:36.000','R1','R1','Finished',NULL,'PO0184')


declare  @sampleReceipt table
(
idno varchar(50), dt datetime, opdesc varchar(10),nxtdate datetime, tnumber varchar(50))
insert @sampleReceipt values ('ABCDE5B12AG1233812345','2018-01-09 06:24:00.000','Received','2020-01-09 06:24:00.000','ABCDE5B12AG123381234543107.2667')
insert @sampleReceipt values ('ABCDE5B12AG1233812345','2018-09-08 22:54:45.000','Received','2020-09-08 22:54:45.000','ABCDE5B12AG123381234555108.5665')
insert @sampleReceipt values ('ABCDE5B12AG1233812345','2018-09-16 16:16:26.000','Received','2020-09-15 22:54:45.000','ABCDE5B12AG12338123453357.6781')
insert @sampleReceipt values ('ABCDE5B12AG1404476','2018-04-10 19:22:29.000','Received','2020-04-10 22:54:45.000','ABCDE5B12AG12338123453198.8073')
insert @sampleReceipt values ('TABCDEFGHIGKLMNOP55','2018-08-24 18:04:31.000','Received','2020-08-24 22:54:45.000','TABCDEFGHIGKLMNOP55.6781')




;with cte as
(
select t1.idno, t1.origidno, t1.recoveridno, t1.dt, t1.lineid, t1.ctype, t1.op_desc, coalesce(t2.tnumber,t3.tnumber,t4.tnumber) as tnumber
from @sampleTrans t1
left join @sampleReceipt t2
on t1.idno= t2.idno and t1.dt between t1.dt and t2.nxtdate
left join @sampleReceipt t3
on t1.origidno= t3.idno and t1.dt between t1.dt and t3.nxtdate
left join @sampleReceipt t4
on t1.recoveridno= t3.idno and t1.dt between t1.dt and t3.nxtdate
)
update @sampleTrans
set tnumber = cte.tnumber
from @sampleTrans st
inner join cte cte
on st.origidno = cte.origidno
and st.dt = cte.dt
and st.op_desc = cte.op_desc


select * from @sampleTrans


Desired Result:
idno----------------origidno----------------recoveridno-----------dt---------------------lineid--ctype---op_desc------	tnumber------------------ponum
======================================================================================================================================================
ABCDE5B12AG12338----ABCDE5B12AG12338--------NULL---------------2018-09-04 19:26:49.000---H1------R2------Create-------ABCDE5B12AG123381234543107.2667--PO0138
ABCDE5B12AG12338----ABCDE5B12AG12338--------NULL---------------2018-09-06 18:41:45.000---H1------R2------Finished-----ABCDE5B12AG123381234543107.2667--	PO0139
ABCDE5B12AG12338----ABCDE5B12AG1233812345---ABCDE5B12AG12338---2018-08-30 08:29:11.000---A1------R1------Create-------ABCDE5B12AG123381234543107.2667--	PO3651
ABCDE5B12AG12338----ABCDE5B12AG1233812345---ABCDE5B12AG12338---2018-08-31 08:35:56.000---A1------R1------Finished-----ABCDE5B12AG123381234543107.2667--	PO3659
ABCDE5B12AG12338----ABCDE5B12AG1233812345---ABCDE5B12AG12338---2018-09-10 08:19:21.000---A2------R1------Create-------ABCDE5B12AG123381234555108.5665--	PO3661
ABCDE5B12AG12338----ABCDE5B12AG1233812345---ABCDE5B12AG12338---2018-09-12 20:52:55.000---A2------R1------Finished-----ABCDE5B12AG123381234555108.5665--	PO3672
ABCDE5B12AG14044----ABCDE5B12AG1404476------ABCDE5B12AG--------2018-04-12 03:32:42.000---H1------R1------Create-------ABCDE5B12AG12338123453198.8073---	PO0180
ABCDE5B12AG14044----ABCDE5B12AG1404476------ABCDE5B12AG--------2018-04-18 03:11:07.000---H1------R1------Finished-----ABCDE5B12AG12338123453198.8073---	PO0181
ABCDE5B12AG14044----ABCDE5B12AG14044--------NULL---------------2018-09-05 20:55:44.000---H2------R2------Create-------TABCDEFGHIGKLMNOP55.6781---PO0185
ABCDE5B12AG14044----ABCDE5B12AG14044--------NULL---------------2018-09-12 09:53:13.000---H2------R2------Finished-----TABCDEFGHIGKLMNOP55.6781---PO0186
ABCDE5B12AG14044----TABCDEFGHIGKLMNOP55-----ABCDE5B12AG--------2018-09-03 14:54:42.000---R1------R1------Create-------TABCDEFGHIGKLMNOP55.6781---PO0183
ABCDE5B12AG14044----TABCDEFGHIGKLMNOP55-----ABCDE5B12AG--------2018-09-03 23:33:36.000---R1------R1------Finished-----TABCDEFGHIGKLMNOP55.6781---PO0184

harishgg1 · September 18, 2018, 1:13pm

Hi

I found the solution to your query

Here is the create data script

Create Data Scropt

drop table #sampleReceipt 
go 

drop table #sampleTrans
go 

create table #sampleTrans 
(
idno varchar(50), origidno varchar(50), recoveridno  varchar(50), dt datetime, lineid varchar(10), ctype varchar(10), op_desc varchar(20), tnumber varchar(50),ponum varchar(10))
go


insert into #SampleTrans values ('ABCDE5B12AG12338','ABCDE5B12AG12338','NULL','2018-09-04 19:26:49.000','H1','R2','Create',NULL,'PO0138')
insert into #SampleTrans values ('ABCDE5B12AG12338','ABCDE5B12AG12338','NULL','2018-09-06 18:41:45.000','H1','R2','Finished',NULL,'PO0139')
insert into #SampleTrans values ('ABCDE5B12AG12338','ABCDE5B12AG1233812345','ABCDE5B12AG12338','2018-08-30 08:29:11.000','A1','R1','Create',NULL,'PO3651')
insert into #SampleTrans values ('ABCDE5B12AG12338','ABCDE5B12AG1233812345','ABCDE5B12AG12338','2018-08-31 08:35:56.000','A1','R1','Finished',NULL,'PO3659')
insert into #SampleTrans values ('ABCDE5B12AG12338','ABCDE5B12AG1233812345','ABCDE5B12AG12338','2018-09-10 08:19:21.000','A2','R1','Create',NULL,'PO3661')
insert into #SampleTrans values ('ABCDE5B12AG12338','ABCDE5B12AG1233812345','ABCDE5B12AG12338','2018-09-12 20:52:55.000','A2','R1','Finished',NULL,'PO3672')
insert into #SampleTrans values ('ABCDE5B12AG14044','ABCDE5B12AG1404476','ABCDE5B12AG','2018-04-12 03:32:42.000','H1','R1','Create',NULL,'PO0180')
insert into #SampleTrans values ('ABCDE5B12AG14044','ABCDE5B12AG1404476','ABCDE5B12AG','2018-04-18 03:11:07.000','H1','R1','Finished',NULL,'PO0181')
insert into #SampleTrans values ('ABCDE5B12AG14044','ABCDE5B12AG14044','NULL','2018-09-05 20:55:44.000','H2','R2','Create',NULL,'PO0185')
insert into #SampleTrans values ('ABCDE5B12AG14044','ABCDE5B12AG14044','NULL','2018-09-12 09:53:13.000','H2','R2','Finished',NULL,'PO0186')
insert into #SampleTrans values ('ABCDE5B12AG14044','TABCDEFGHIGKLMNOP55','ABCDE5B12AG','2018-09-03 14:54:42.000','R1','R1','Create',NULL,'PO0183')
insert into #SampleTrans values ('ABCDE5B12AG14044','TABCDEFGHIGKLMNOP55','ABCDE5B12AG','2018-09-03 23:33:36.000','R1','R1','Finished',NULL,'PO0184')
go

create table #sampleReceipt 
(
idno varchar(50), dt datetime, opdesc varchar(10),nxtdate datetime, tnumber varchar(50))

insert into #sampleReceipt values ('ABCDE5B12AG1233812345','2018-01-09 06:24:00.000','Received','2020-01-09 06:24:00.000','ABCDE5B12AG123381234543107.2667')
insert into #sampleReceipt values ('ABCDE5B12AG1233812345','2018-09-08 22:54:45.000','Received','2020-09-08 22:54:45.000','ABCDE5B12AG123381234555108.5665')
insert into #sampleReceipt values ('ABCDE5B12AG1233812345','2018-09-16 16:16:26.000','Received','2020-09-15 22:54:45.000','ABCDE5B12AG12338123453357.6781')
insert into #sampleReceipt values ('ABCDE5B12AG1404476','2018-04-10 19:22:29.000','Received','2020-04-10 22:54:45.000','ABCDE5B12AG12338123453198.8073')
insert into #sampleReceipt values ('TABCDEFGHIGKLMNOP55','2018-08-24 18:04:31.000','Received','2020-08-24 22:54:45.000','TABCDEFGHIGKLMNOP55.6781')
go

Here is the SQL

New SQL

UPDATE a 
SET    a.tnumber = b.tnumber 
FROM   #samplereceipt b 
       JOIN #sampletrans a 
         ON b.idno LIKE '%' + a.idno + '%' 
             OR b.idno LIKE '%' + a.origidno + '%' 
             OR b.idno LIKE '%' + a.recoveridno + '%'

Here is the result

Villanuev · September 18, 2018, 11:04pm

Hi harishgg1.. thanks for the reply. Just run the query and i'm getting incorrect distribution of data for tnumber specially for origidno='ABCDE5B12AG14044' the tnumber should be TABCDEFGHIGKLMNOP55.

I have another desired result if this can be work is to create another column for new_origidno.

Desired Result2:
idno----------------origidno----------------recoveridno--------new_originalidno-------------dt---------------------lineid--ctype---op_desc------	ponum
======================================================================================================================================================
ABCDE5B12AG12338----ABCDE5B12AG12338--------NULL---------------ABCDE5B12AG1233812345--------2018-09-04 19:26:49.000---H1------R2------Create---------PO0138
ABCDE5B12AG12338----ABCDE5B12AG12338--------NULL---------------ABCDE5B12AG1233812345--------2018-09-06 18:41:45.000---H1------R2------Finished-----	PO0139
ABCDE5B12AG12338----ABCDE5B12AG1233812345---ABCDE5B12AG12338---ABCDE5B12AG1233812345--------2018-08-30 08:29:11.000---A1------R1------Create-------	PO3651
ABCDE5B12AG12338----ABCDE5B12AG1233812345---ABCDE5B12AG12338---ABCDE5B12AG1233812345--------2018-08-31 08:35:56.000---A1------R1------Finished-----	PO3659
ABCDE5B12AG12338----ABCDE5B12AG1233812345---ABCDE5B12AG12338---ABCDE5B12AG1233812345--------2018-09-10 08:19:21.000---A2------R1------Create-------	PO3661
ABCDE5B12AG12338----ABCDE5B12AG1233812345---ABCDE5B12AG12338---ABCDE5B12AG1233812345--------2018-09-12 20:52:55.000---A2------R1------Finished-----	PO3672

ABCDE5B12AG14044----ABCDE5B12AG1404476------ABCDE5B12AG14044---ABCDE5B12AG1404476-----------2018-04-12 03:32:42.000---H1------R1------Create-------	PO0180
ABCDE5B12AG14044----ABCDE5B12AG1404476------ABCDE5B12AG14044---ABCDE5B12AG1404476-----------2018-04-18 03:11:07.000---H1------R1------Finished-----	PO0181
ABCDE5B12AG14044----ABCDE5B12AG14044--------NULL---------------TABCDEFGHIGKLMNOP55----------2018-09-05 20:55:44.000---H2------R2------Create--------PO0185
ABCDE5B12AG14044----ABCDE5B12AG14044--------NULL---------------TABCDEFGHIGKLMNOP55----------2018-09-12 09:53:13.000---H2------R2------Finished-----PO0186
ABCDE5B12AG14044----TABCDEFGHIGKLMNOP55-----ABCDE5B12AG14044---TABCDEFGHIGKLMNOP55----------2018-09-03 14:54:42.000---R1------R1------Create-------PO0183
ABCDE5B12AG14044----TABCDEFGHIGKLMNOP55-----ABCDE5B12AG14044---TABCDEFGHIGKLMNOP55----------2018-09-03 23:33:36.000---R1------R1------Finished-----PO0184

harishgg1 · September 19, 2018, 3:51am

Hi

I can write the SQL in a lot of different ways and even hard code

It will break if your data is different

Need to know the logic you want to use ???

Thanks

Villanuev · September 19, 2018, 4:04am

THank for the reply. I created a new post. I try a different approach. Can you please take a look my new post. need to find the nearest dt and expose/created or display the associated column/field. thank you.

This is the title of the post: Find the closest date with associated column / field