Click here to Skip to main content
15,843,200 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello friend I am trying to build web application for account reconciliation & now I am stuck after importing data. I have below mentioned query which can individually separate common string but I want to apply this to whole table please help.

I have two tables with data narration,debit,credit and match.
In one table narration is like
NEFT/N292190958364579/00019049/NILESH R DESAI

and in another table narration is
To-Trf. RTGS : BAJAJ FINANCE LIMITED 191 Cr A/c No 1011132001850 UTR N292190958345310

I want to match N292190958345310 in both tables and update match as 0.

If any table have duplicate value thank nothing should be updated. narration structure is fix.

What I have tried:

SQL
declare @mysql VARCHAR(MAX);


set @mySQL ='To-Trf. RTGS : BAJAJ FINANCE LIMITED 191 Cr A/c No 1011132001850 UTR N292190958345310'

Select (Case When PATINDE'%Beneficiary%', @mySQL) <> 0 Then SUBSTRING(@mySQL, PATINDE'%UTR%', @mySQL) + 4,CHARINDE'Beneficiary',@mySQL)-CHARINDE'UTR',@mySQL)-4) Else Substring(@mySQL, PATINDE'%UTR%', @mySQL) + 4, LEN(@mySQL)) End) As NewString


set @mySQL ='NEFT/N292190958364579/00019049/NILESH R DESAI'
SELECT SUBSTRING(@mySQL,CHARINDE'/',@mySQL)+1, CHARINDE'/',@mySQL,CHARINDE'/',@mySQL)+1) - CHARINDE'/',@mySQL)-1)
Posted
Updated 15-Apr-20 6:27am
v3
Comments
Maciej Los 15-Apr-20 10:55am    
"I want to match ... and update match as 0" - what do you mean?
In both tables?
I do not see 'N292190958345310' in a table #1.

1 solution

Please, read my comment to the question first.

I've created sample script. Feel free to change it to your needs.

SQL
DECLARE @table1 TABLE(narration NVARCHAR(100), [match] int)
INSERT INTO @table1(narration)
VALUES('NEFT/N292190958364579/00019049/NILESH R DESAI'),
('NEFT/N292190958345310/00019049/NILESH H DELHI'),
('NEFT/N292190958345325/00019049/NILESH O BARAI')

DECLARE @table2 TABLE(narration NVARCHAR(100), [match] int)
INSERT INTO @table2(narration)
VALUES('To-Trf. RTGS : BAJAJ FINANCE LIMITED 191 Cr A/c No 1011132001850 UTR N292190958345310'),
('Ot-Rtf. RSGS : DESAI FINANCE LIMITED 195 Cr A/c No 1011132001880 UTR N292190958345380'),
('Do-Zff. RTTS : BARAI FINANCE LIMITED 111 Cr A/c No 1011132001855 UTR N292190958345315')

DECLARE @common TABLE(narr1 NVARCHAR(100), s1 NVARCHAR(100), s2 NVARCHAR(100), narr2 NVARCHAR(100))

INSERT INTO @common(narr1, s1, s2, narr2)
SELECT t1.narration as narr1, t1.s1, t2.s2, t2.narration AS narr2
FROM
(
	SELECT narration, SUBSTRING(narration, CHARINDEX('/', narration)+1, CHARINDEX('/', narration, CHARINDEX('/', narration)+1)-CHARINDEX('/', narration)-1) AS s1
	FROM @table1
) t1 INNER JOIN
(
	SELECT narration, RIGHT(narration, CHARINDEX(' ', REVERSE(narration))-1) AS s2
	FROM @table2
) t2 ON t1.s1 = t2.s2


UPDATE t1
	SET t1.[match] = 1 
FROM @table1 t1 INNER JOIN @common t2
	ON t1.narration = t2.narr1 

SELECT *
FROM @table1 


Note: i'm using temporary tables (stored in variables).
@common table is used to grab common data, then to update table #1.
In the same manner, you can update table #2.

Good luck!

Result:
narration	                                   match
NEFT/N292190958364579/00019049/NILESH R DESAI	NULL
NEFT/N292190958345310/00019049/NILESH H DELHI	1
NEFT/N292190958345325/00019049/NILESH O BARAI	NULL
 
Share this answer
 
v2
Comments
Hemil Gandhi 8-Jul-20 6:34am    
Hey buddy now I am stuck with another issue, some times @common appears twice in one table & single time in another table. Now how to deal with it??, No entry should be updated in both tables.
Maciej Los 8-Jul-20 6:48am    
SELECT DISTINCT should help.
Hemil Gandhi 8-Jul-20 6:59am    
I am using below mentioned query to match & update record. Plz guide on this

UPDATE t1 SET t1.[Recon] = 1 ,t1.[ReconDT]=getdate() FROM TEMP_AXIS t1 INNER JOIN TEMP_SBPP t2 ON t1.UTR = t2.UTR and t1.CrDr='CR' AND CAST(T1.Amount AS DECIMAL(10,2))=CAST(T2.dr AS DECIMAL(10,2)) and t1.[Recon] is null and t2.[Recon] is null
Maciej Los 8-Jul-20 7:25am    
How this query is related to my answer?
As i mentioned, if you would like to get distinct data, you need to use DISTINCT keyword (you can use subquery for such of requirement).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900