Reconciliation query web application VB.NET

Question

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 10-Apr-20 22:12pm

Hemil Gandhi

Updated 15-Apr-20 5:27am

v3

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2020-04-15T05:28:00

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