SQLTeam.com | Weblogs | Forums

Need to match 2 columns from 2 tables


#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


#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'%')