Comparing text column on the same table

Database

Comparing text column on the same table

Hi
I have a table Tax_Transaction containing following columns:

Transaction_ID

Client_First_Name

Client_Last_Name

Client_Tax_Number

Client_Business _Number

Client_I_B_Number

Transaction_Event_Date

 

I have to find all tranactions that have similar first names for the last 30 days. The query I am using is:

 

with monthly_activity as (
  select

    transaction_id,
   Client_Tax_Number,
    Client_Business _Number,
    Client_I_B_Number,
    Client_First_Name,
    Client_Last_Name
  from  Tax_Transaction
  where Transaction_Event_Date >= Current_Date - 30    
)
select
a.transaction_id, b.transaction_id,
a.Client_First_Name,b.Client__First_Name,
a.Client_Last_Name,b.Client_Last_Name,
TD_SYSFNLIB.EDITDISTANCE(a.Client_First_Name,b.Client_First_Name,2,1,1,2) as score_gn,
TD_SYSFNLIB.EDITDISTANCE(a.Client_Last_Name,b.Client_Last_Name,2,1,1,2) as score_fn
from monthly_activity a
join monthly_activity b
  on a.Client_Business_Number = b.Client_Business_Number

    and a.Client_I_B_Number = b.Client_I_B_Number

        where TD_SYSFNLIB.EDITDISTANCE(a.Client_First_Name,b.Client_First_Name2,1,1,2) <> 0
and TD_SYSFNLIB.EDITDISTANCE(a.Client_Last_Name,,b.Client_Last_Name,2,1,1,2) <> 0
and a.Request_Event_Date_Time < b.Request_Event_Date_Time

and a.Client_Tax_Number <> b.client_Tax_Number

and a.Transaction_Id <> b.Transaction_Id

and a.Transaction_Event_Date >= Current_Date - 30

and a.Transaction_Event_Date < b.Transaction_Event_Date

 

When I run the above query the execution spools the temp space. Is there an efficient way than the above to achieve the output. Thank you in advance.

Sam

1 REPLY
Enthusiast

Re: Comparing text column on the same table

Does this work?

SELECT * FROM Tax_Transaction
WHERE Client_Business _Number
, Client_I_B_Number IN 
(
	SELECT Client_Business _Number
	, Client_I_B_Number
	FROM Tax_Transaction
	WHERE Transaction_Event_Date >= CURRENT_DATE-30
	GROUP BY Client_Business _Number
	, Client_I_B_Number
	HAVING COUNT(DISTINCT Client_First_Name)=1
) T1