Need to match 2 columns from 2 tables

Transact-SQL
1

hi please help me to match 2 columns(1.a,2.a) from 2 tables(1,2)
with the condition 1.a contains 2.a

eg:
if
1.a = Ret to carrier for some reason
2.a = ret to carrier

i need match this columns and get output as matched

2

Please provide data script

3

if the
comments column Contains "ret to Carrier" , "Rejected", "Please provide", or "invoice need"
and type column = Returned
then i should update
action column as Dispute
and
type column as Missing Supporting Docs

else
if the
comments column Contains "ppd", "Collect" or "Prepaid"
and type column = Returned
then i should update
action column as Dispute
and
type column as Incorrect Party

4

hi i understand what you are saying

Please provide data script
drop table #data
go
create table #data
(
column1 int ,
column2 varchar(100)
)
go
insert into #data select 1,'harish'
insert into #data select 2,'gokul chat netgear wifi router off ..checking ..'
go

6

drop table #data
go
create table #data
(
comment nvarchar(max) ,
type# nvarchar(max)
)
go
insert into #data select 'Ret to carrier for bill for returned to:','return'
insert into #data select 'ppd to d danfoss in nordbork','return'
insert into #data select 'need to Collect from customer','hold'
insert into #data select 'please provide more detail','return'
insert into #data select 'invoice need to progress more','queued'
go

if the
comments column Contains "ret to Carrier" , "Rejected", "Please provide", or "invoice need"
and type# column = Returned
then i should update
action column as Dispute
and
type column as Missing Supporting Docs

else
if the
comments column Contains "ppd", "Collect" or "Prepaid"
and type# column = Returned
then i should update
action column as Dispute
and
type column as Incorrect Party

7

Hi

Please find the work I did below

If it looks ok

I can work on the UPDATE Statement

Create Data Script 
use tempdb 
go 

drop table #data
go

create table #data
(
comment nvarchar(max) ,
type# nvarchar(max)
)
go

insert into #data select 'Ret to carrier for bill for returned to:','return'
insert into #data select 'ppd to d danfoss in nordbork','return'
insert into #data select 'need to Collect from customer','hold'
insert into #data select 'please provide more detail','return'
insert into #data select 'invoice need to progress more','queued'
go
SQL 
SELECT comment, 
       type#, 
       CASE 
         WHEN comment IN ( 'Ret to carrier for bill for returned to:', 
                           'Rejected', 
                                             'please provide more detail', 
                                             'invoice need to progress more', 
                           'ppd', 'Collect', 'Prepaid' ) 
              AND type# = 'return' THEN 'Dispute' 
       END AS action_column, 
       CASE 
         WHEN comment IN ( 'Ret to carrier for bill for returned to:', 
                           'Rejected', 
                                             'please provide more detail', 
                                             'invoice need to progress more' ) 
              AND type# = 'return' THEN 'Missing Supporting Docs' 
         WHEN comment IN ( 'ppd', 'Collect', 'Prepaid' ) 
              AND type# = 'return' THEN 'Incorrect Party' 
       END AS type_column 
FROM   #data 

go
Result

image
image.png1470×455 10.8 KB

8

hi harishgg thank you.. this looks good.... i can work on update statement.. Thank you so much :slight_smile:

9

Hi Harishgg,

we have n number of comments which contains these words.. but i think this will not work.. this can be done only for the known comments rite!!!

10

When you say comments contain these words ..it's possible
You have to use like

Comments like '%word1%'

Comments like '%word2%'

11

Yes harishggg you are rite... instead i used comments like concat('%'col1'%')