-- create the MAIN DATA TABLE and fill it with some junk.
create table MainData( id int, f1 varchar(255), f2 varchar(255), f3 varchar(255), f4 varchar(255));

insert into MainData values (1, 'Sunita', 'Beck', 'Stamford', 'CT');
insert into MainData values (2, 'Jaya','Gopal', 'Greenwich', 'CT');
insert into MainData values (3, 'Josh','Murry', 'New York', 'NY');

-- create the TEST DATA TABLE and fill it with some other junk.
create table NewData(id int, f1 varchar(255), f2 varchar(255), f3 varchar(255), f4 varchar(255));
insert into NewData values (1, 'Sunita', 'Smith', 'New York', 'NY');
insert into NewData values (2, 'Jaya','Gopal', 'Greenwich', 'CT');
insert into NewData values (3, 'Josh','Smith', 'Hartford', 'CT');

-- see the raw junk.
select * from MainData;
select * from NewData;

-- Do the field matching calculation.
with cte as
(
   select 
      case when n.f1 = m.f1 then 1 else 0 end +
      case when n.f2 = m.f2 then 1 else 0 end +
      case when n.f3 = m.f3 then 1 else 0 end +
      case when n.f4 = m.f4 then 1 else 0 end as MatchingFieldCount,
      n.*
   from
      NewDAta n cross join MainDAta m  
)
select
   max(MatchingFieldCount) as BestMatchFieldCount,
   max(MatchingFieldCount)*100/4.0 as BestMatchPercent, -- dividing by 4 because we are matching on 4 fields.
   id,f1,f2,f3,f4
from
   cte
group by
   id,f1,f2,f3,f4
order by
   id;

-- clean up the junk.
drop table    MainData;
drop table    NewData;

NewDAta n cross join MainDAta m  

(	
	NewData1 n1 
	inner join NewData2 n2 on n2.acct_cd = n1.acct_cd -- I am making it up. Instead of acct_cd use what you need to join on.
	inner join NewData3 n3 on n3.order_id = n1.order_id -- Again, making it up.
) 
cross join
(
	Maindata1 m1
	inner join MainData2 m2 on m2.trade_Id = m1.trade_id
)

quote:

---

Originally posted by aahmed1997

nothing works but thank you for everyone's help

---

with cte as
(
select 
   case when n.Physical_address_1 = m.Phys_add_1 then 1 else 0 end +
   case when n.accnt_nm = m.accnt_nm then 1 else 0 end +
   case when n.City = m.Phs_city then 1 else 0 end +
   case when n.Major_accnt_cd = m.Mac_code then 1 else 0 end +
   case when n.[IBS DUD] = m.Taxpayer_Registration_Code then 1 else 0 end + 
   case when n.cntct_ph_no = m.cntct_ph_no then 1 else 0 end + 
   case when n.billaddress1 = m.Bill_address1 then 1 else 0 end as MatchingFieldCount,
   
   -- from here on down, list ONLY columns from the new data table.
   n.accnt_no as TmpAccnt, 
   n.Physical_address_1 as Phys_address, 
   n.Billaddress1 as BillAddress1, 
   n.City as TmpCity, 
   n.accnt_nm as Tmp_Name,
   n.Major_accnt_cd as tmpMAC_Code, 
   n.[IBS DUD] as ibs_DUD, 
   n.cntct_ph_no tmp_Ph
from 
   #tmp n cross join #tmp2 m 
)
select
   max(MatchingFieldCount) as BestMatchFieldCount,
   max(MatchingFieldCount)*100/7.0 as BestMatchPercent,
   TmpAccnt, 
   Phys_address, 
   BillAddress1, 
   TmpCity, 
   Tmp_Name, 
   tmpMAC_Code, 
   Ibs_DUD, 
   tmp_Ph
from
   cte
group by 
   TmpAccnt, 
   Phys_address, 
   BillAddress1, 
   TmpCity, 
   Tmp_Name, 
   tmpMAC_Code, 
   Ibs_DUD, 
   tmp_Ph

-- step 1. After you have created and populated #tmp, add a column to it 
-- with this command. We are adding a serial number column, which we will need.
alter table #tmp add SNMId int not null identity(1,1);

-- now do the CTE thingie just as before, but include the SNMId in the select
-- list. See the below in red.
with cte as
(
select 
   case when n.Physical_address_1 = m.Phys_add_1 then 1 else 0 end +
   case when n.accnt_nm = m.accnt_nm then 1 else 0 end +
   case when n.City = m.Phys_city then 1 else 0 end +
   case when n.Major_accnt_cd = m.mac_code then 1 else 0 end +
   case when n.[IBS DUD] = m.Taxpayer_Registration_Code then 1 else 0 end + 
   case when n.cntct_ph_no = m.contact_ph then 1 else 0 end + 
   case when n.billaddress1 = m.Bill_add_1 then 1 else 0 end as MatchingFieldCount,
   
   -- put pretty much any column from #tmp or #tmp3 that you want.
   n.accnt_no as TmpAccnt, 
   m.accnt_no as IBS_Accnt, 
   n.Physical_address_1 as Phys_address, 
   m.Phys_add_1 as IBS_Phy_Address,
   n.Billaddress1 as BillAddress1, 
   m.bill_add_1 as IBS_Bill_address, 
   n.City as TmpCity, 
   m.BillCity as ibs_City, 
   n.accnt_nm as Tmp_Name, 
   m.accnt_nm as IBS_Name, 
   n.Major_accnt_cd as tmpMAC_Code, 
   m.MAC_code, 
   n.[IBS DUD] as ibs_DUD, 
   m.Taxpayer_Registration_Code, 
   n.cntct_ph_no as tmp_Ph, 
   m.contact_ph,
   n.SNMId
from 
   #tmp n cross join #tmp3 m 
),
-- Now we want to order the result set from the cte to pick the best match.
cte2 as
(
   select
      row_number() over (partition by SNMId order by MatchingFieldCount desc) as rowId,
      MatchingFieldCount*100/7.0 as BestMatchPercent, 
      *
   from
      cte
)
-- And, the best match would have rowId = 1, so select that.
select
   *
from
   cte2
where rowId = 1;